Chart that reads results of multiple drop down boxes. Can someone please take a look.

[Marq]

Using Excel 2007

I have six colums (Date; Skill; ST Hrs; OT hrs: Area; Type). I copied and pasted below a small portion of info from the spreadsheet. I know when I copy and paste into a post the information under the headers don't line up exactly...it appears offset, so basically "Date" is of course the date you see..."Skill"'s columns has the first item as nb-mech b..."ST Hrs" first item will be 0.00..."OT Hrs" will be 1.50...."Area"'s first item is "Log FM NO"....and "Type"'s first item is MX07

And there are 17,000 rows of this info and forever growing...a lot of repeated items in each column such as you can see "blaster" has multiple entries on 4/1/2012.

What I am attempting to do which I have not firgured out yet (i have tried array formulas but they bog down the pc forver and couldn't figure out exactly how to make it give me the results I needed...and I also tried sumproducts but that didnt get me what I wanted....and it is being requested of me to NOT make a pivot table but a chart.

What I am trying to create is is the following:

1 - cells to plug in a date range for example cell A1 I plug 4/1/2012 and cell A2 plug 4/30/2012
2 - drop down boxes for columns Skill; Area: Type, which show only unique records in each column

Once I have that, I could then filter all 3 itmes and have only items show in the date ranges I picked and present ONLY the SUMS of columns ST Hrs and OT hrs.

Once the SUMS show up, I can then have cells off to the side that will give a percent break down of what percent of the total hours (for the chose criteria) are ST Hrs and what percent of the total are OT Hrs....and this percentage break down will show up in a Bar Chart, per day chosen in my date ranges....I'm thinking one simple stacked bar per day in the chart.

So say, based on the info below, I wanted to see the total ST Hrs and OT Hrs from 4-1-12 to 4-2-12 for a nb-blaster in LOG FM NO and type MX06.....my result would be 0.00 ST Hrs...23 OT Hrs.....and my bar chart would be one simple bar peaked out at 23 on day 4-1-12.....and within the bar it would be 0.00% ST Hrs and 100% OT Hrs and maybe the total hours for each showing also..don't need that but it may be useful.. ..and no bar for 4-2-12 (because the criteria picked had no hours for 4-2-12)

I have been messign witht is all week and just can't nail it..hopefully I can get some help on here. I did my best to explain in the simplest way. I do remember a while ago I would have been able to load the spreadsheet into the post and people could see it in excel...is that option to do that no longer on this site?



Date Skill ST Hrs OT Hrs Area Type

4/1/2012 nb - mech b 0.00 1.50 Log FM NO MX07
4/1/2012 nb - scaffolding 0.00 8.50 Bogs NO MX02
4/1/2012 nb - coatings mech b 0.00 3.00 Log FM NO MX07
4/1/2012 nb - blaster 0.00 11.50 Log FM NO MX06
4/1/2012 nb - blaster 0.00 3.00 Log FM NO MX07
4/1/2012 nb - blaster 0.00 11.50 Log FM NO MX06
4/1/2012 nb - helper 1 0.00 3.00 Log FM NO MX07
4/1/2012 nb - helper 1 0.00 3.00 Log FM NO MX07
4/1/2012 nb - scaffolding 0.00 1.00 Bogs NO MX02
4/2/2012 painter 10.00 0.00 Furnaces NO MX04
4/2/2012 inspect qa/qc supv 6.50 0.00 CUI NO
4/2/2012 nb - scaffolding 3.50 0.00 Turnaround NO MX08
4/2/2012 nb - scaffolding 1.00 0.00 7960
4/2/2012 nb - scaffolding 1.50 0.00 Turnaround NO MX06
4/2/2012 planner 1 5.25 0.00 CUI NO
4/2/2012 refractory technician 5.00 0.00 Large PM NO MX07
4/2/2012 nb - scaffolding 1.00 0.00 West Area NO MX09
4/2/2012 nb - scaffolding 6.25 0.00 VicknairE NO MX07
4/2/2012 nb - scaffolding 1.00 0.00 West Area NO MX02
4/2/2012 nb - scaffolding 1.50 0.00 Turnaround NO MX06
4/2/2012 nb - scaffolding 1.00 0.00 Log FM NO MX06
4/2/2012 nb - mech b 5.00 0.00 VicknairE NO MX07
4/2/2012 nb - scaffolding 4.75 0.00 Utilities NO MX06

 

[mathchick]

Have you thought about trying a pivot table? It's got built in ways of quickly doing a lot of what you described.

 

[pplstuff]

PivotTables are handy, but they aren't dynamic, so if you change data you'll have to make a new pivottable.

If I understand correctly: you have 17,000 rows of data, and you're looking to make a chart that will filter the data based on 1.start date, 2.end date, and 3.some attribute of your data.

To make a dynamic chart, you should start my making a dynamic table. That is, you need a table where all the values will change depending on the 3 variables you select. The chart should reference this dynamic table you create. So you're never really changing the chart, you're changing the table and the chart changes as a result.

There are many different way to approach this, you will have to determine which will work best for you. For the drop box, you can use Data Validation->list or you can use a Combo Box ->this will require more work, but may be more functional. the function you use in the table will depend on your technique, but I'd imagine you'll use function like vlookup, index, match, offset, indirect, etc.

 

[mathchick]

PivotTables are handy, but they aren't dynamic, so if you change data you'll have to make a new pivottable.

Whoa! There's a refresh pivot table in the pivot table banner. You don't have to remake it each time!

 

[pplstuff]

I wish I would've known that months ago, lol.

 

[mathchick]

I wish I would've known that months ago, lol.

Awwww, I'm sorry. Yeah I think I learn the most when I post responses to other people's questions and then other people correct my corrections.