Varying Graph from Filtering Data

**cjsec9** · 10-13-2008, 01:15 PM

I've created two large historical tables on separate sheets. The master sheet (Bins) contains the following columns:
Year - Side - Commodity - Variety - Block and then each consecutive column is a day of the year, starting at 4/26 and going through 10/31 (F - GL). Column GM is a sum of all days in each row. For example, GM15 totals F15:GL15. The second sheet (Percentages) is identical to the first in size and format, however, it calculates daily percentages of totals from the first sheet. For example, cell BA15 contains the formula

Please Login or Register  to view this content.

to provide what percentage that day was of the total line.

These are very large tables (1400 rows x 195 columns), and will only grow larger as time goes on and new years are added historically. I need to graph this data based on the way the table is filtered. For example, if I want to see a graph of all 2004, east side, peach, peach type 1, is there a way to use the Data filters and have a graph automatically pick up my filtering choices and plot them?

Also, is it possible to have a set of input cells that a graph calls to which then applies what is input as filters to the data table and formats them? For example, on a sheet with only the graphs and one row for input, can I input "2004" "East" "Peach" "Peach Type 1", and have it automatically pull only that data from the table and plot it?

**robert111** · 10-14-2008, 11:20 AM

See attachment, does this get anywhere near what you want?

**cjsec9** · 10-14-2008, 12:22 PM

Thanks Robert. It does help, actually, a lot. I tried using sumproducts before, but for some reason they weren't working. I didn't have them paired with data validation lists, though. The only problem I run in to now is if I want to be more general with my graph, such as looking at all Peaches, or all Apples, I need to have a separate set of validations and sumproducts and a separate graph to view it, correct?