Excel 2007 : Switching Data Sets using a Check Box

I have a data set made up from cells A1:CK6000 which is used to feed numerous charts and a data table. Columns CM to CP act as data filters in that, depending on the entries in Columns D, Column CM will read either 'Yes' or 'No', so I can filter out various options from the sheet. The same is the case for Columns CN to CP, filtering out options in other columns. Effectively CM to CP allows the source data to be filtered in a combination of different ways.

Having built the front end to the raw data I've now been asked to incude various filtered data options in additition to the raw data. The filtered data will affect all 200 charts and the figures in the data table.

I can obviously write various SUMIFS formula to filter the data based on the entries in columns CM to CP, but I'm trying to find a way to then use this filtered data to update all my charts and data table on my 'dashboard'. I would like to be able to simply add a few checkboxes, so the user can check them according to what they want to see and have the existing data somehow update automatically or for the dataset to switch between different data sets, but I really have no idea the best way to approach this and I would be grateful for any help, ideas or suggestions.

As an example I use my raw data to calculate totals using a formula like this:

Please Login or Register  to view this content.

To add one of the required filters, I would amend my formula to this:

Please Login or Register  to view this content.

By doing this, every total in my original data set will change (this affects around 540,000 entries) and I need the user to be able to view the charts using both the raw, unfiltered data (as they are currently) and then the same charts using the filtered data. I should menition that the user only has access to the front end 'user interface/dashboard' and not the underlying data.

All my charts and my data table are currently based on the first set of formula. My quesion is, how can I now add a checkbox (or whatever) to allow the user to now filter the data or switch the data (or whatever) to display all the results using the filtered data set?

I hope this makes sense...

I would be grateful for any help, ideas, suggestions as to the easiest way to approach this problem.

Even if I build numerous, completely new data sets based on the filtered data, how can I switch between the various data sets using a check box or is there a much simpler approach? I would ideally prefer NOT to have to build a new data set for each filter option but to somehow use the raw data set and be able to do somethig clever with filters/formula, bearing in mind there are some extremely lengthy formula in place already.

Many thanks for any help, advice, pointers, guidance, examples...

Maybe you can work with a pivot table.

Make the check box select value of the 2nd condition. Hence if the checkbox isnt checked the formula should reflect

=SUMIFS(AR8:AR6000,A8:A6000,"London",CM8:CM6000,"*")

If checked

=SUMIFS(AR8:AR6000,A8:A6000,"London",CM8:CM6000,"Genuine")

So based on checking,non-checking of the checkbox, it'll toggle between "Genuine" & "*"

One limitation though is that =SUMIFS(AR8:AR6000,A8:A6000,"London",CM8:CM6000,"*") will not sum values where only 1st condition "London" is met and the corresponding 2nd condition column is blank.

Nope, no need for a second data set. The idea is to make the checkbox pick either "Genuine" or "*".

I have uploaded a sample worksheet depicting the same
See if you can adapt this to your worksheet

Will be glad to see it come to life. Good luck and yes do write back with any feedback/queries!

Don't you think another separate checkbox would do the trick? From what I understand, there are 2 conditions which can either be met/not met, which gives us 4 scenarios
1. TRUE,TRUE
2. TRUE,FALSE
3. FALSE,TRUE
4. FALSE,FALSE

You could then use something on the lines of

=IF(AND(AM2=TRUE,AN2=TRUE),"Genuine",IF(AND(AM2=TRUE,AN2=FALSE),"???",IF(AND(AM2=FALSE,AN2=TRUE),"???!!","*")
))

Great! Yep, you could do it with radio buttons.

As you aptly put it, simple yet effective! Good luck. God bless!