Filtering a table

[Finalfrontier1976]

Afternoon all,

I've created a table (which I intend to expand on) which has in it past MPG results year and month and by depot.

What I am trying to do is create a filter and a bar graph so I can filter the table by depot and month to compare results and get the bar graph to respond accordingly. I'd like the option to compare any month in a year or possibly the same month in a couple of years (say January) for each depot then if I wanted just see the results for the whole year or two for one particular depot. If that makes sense.

I can get the one filter but can't seem to work out how to create a secondary filter. I've created 2 tables to try 2 different ways but no success. Can anyone have a look and suggest.

Thanks,
Lew

[MrShorty]

My first impression from your spreadsheet is that this looks an awful lot like a pivot table, so naturally I think of how a pivot table should make this easy.

If this is an Excel pivot table, you should simply need to create the chart based on the table, then use the "column filters" and "row filters" dropdowns to filter out the desired depots and months.

If this is a pivot table generated by some other source, then you might look at the filter tools available from that application.

If this is not really a pivot table, but you have access to the original "database" this is based on, then go back to that data, generate a pivot table/pivot chart based on the original source and create an Excel pivot table/chart from the original source database.

If this is not a pivot table and you do not have access to a good "database" format for this data, use the procedure here (http://www.excelforum.com/showthread...=1#post4464672 ) to "unpivot" the data and recover something more like the original database. Then build a pivot table/chart from that data.

Basically, I think the easiest way to get what you want is some variation of "get the data into a good database format and create a pivot table/chart from that".

[Finalfrontier1976]

Hi,

No, it may look that way but this is just a normal table with filters, not a pivot table. I can filter it to show me a certain month for every depot but I'd like to also filter on a singular depot as well as more than one depot to compare.

The month filter is fine, the depot one is the filter I'm having trouble figuring out.

[MrShorty]

I still think some variation of "convert existing table to pivot table" is the easiest way. Which of those scenarios best describes what you have? If you don't have access to the source data, what did you think of the last "unpivot then pivot" approach?

[Finalfrontier1976]

The table on that spreadsheet is the source data, I've just typed in the values manually and added a filter. Going forward I would do the calculations and type in the results in this table.

[MrShorty]

Perhaps I am just being stubborn, but are you truly dead set against managing this as a database + pivot table? I quickly put 3 depot's worth of data into a list/database (I don't have a nice unpivot utility like described in the other thread), then built a pivot table off of that. I can filter by month and/or year (using the row labels dropdown) and I can filter by depot (using the column labels dropdown). Maybe I am just stubborn, but I see a lot of advantages to this kind of "database + pivot" approach to this kind of data management.

There are other ways to approach this problem, to be sure. I think this approach has advantages (especially if the database can grow in the future) that make me want to stubbornly suggest it one more time before looking at the other possible approaches.

[Finalfrontier1976]

Thanks,

I'll have a look and apply your example to the data I have and see what comes out.

Thanks again for your help.