Modify formulas on the entire sheet based on dropdown selection

I understand that there is a 7 if max when it comes to nested if statements in excel but i have 11 categories i would like to have in a dropdown menu.

I have a sheet which is a daily report dashboad that contains around 120 formulas which pulls data from two other sheets. But now i need to categories this dashboard so i thought it might be a good idea to have a dropdown so rather than create 11 different sheets/reports i would like to simply add a dropdown to change the data. Basically i just need to add an extra criteria to each of the 120 sumifs formulas based on the dropdown.

As there are 11 categories a simple if statement will not work, so i am wondering if there is an alternative method for me to produce the same results?

Any help will be much appreciated

Cheers

You can make a table of the of the 11 categories in column an consecutive numbers from 1 to 11 in column B on a separate worksheet. Then use the Choose function to look up the number and perform a specific formula based on it. so the formula would look something like this:

Formula:

Please Login or Register  to view this content.

This is easily expandable in the future. Just had option 12 to the new options list and put another comma in the choose field.

Does this work?

Hi Spitzerpl,

I've never used the 'Choose' function before, but from what you are saying it looks like it might be the perfect solution for me. Many thanks for your input, i will give it a try and let you know how it goes.

Cheers

Originally Posted by Spitzerpl

use the Choose function to look up the number and perform a specific formula based on it.