Sum data based on selection on dropdown menu

Since you have a database which keeps expanding (assumption) then it is best to create first some dynamic named ranges so that you references can remain dynamic.

So first create a named range for each of the Actual, Budget and Forecast sheets.

e.g use the same name as the sheetnames, you can use a name: Actual and apply formula:

Please Login or Register  to view this content.

for Budget:

Please Login or Register  to view this content.

and for Forecast:

Please Login or Register  to view this content.

then in Summary sheet, D6 use formula:

Please Login or Register  to view this content.

copied down and across.. then replace Actual with Budget and Forecast, respectively in column E and F.

in Summary, L6 use:

Please Login or Register  to view this content.

copied down and across.. then replace Actual with Budget and Forecast, respectively in column M and N.

Hi,

Thanks for the quick response. It works well.

Just trying to understand how the formula works.
1. I moved the reference (Expense category) to column D and the formula just stop working.
2. With the dynamic name, means that I simply insert another column for additional data e.g Aug 2012 and it will automatically included in the range, right?

The Named Ranges and formulas in the Summary tab were based on the Expense Category being the last column in each sheet...

you should be able to insert columns between the date columns and the Expense Category.. and the formulas should all self adjust.

However, if you want to move Expense Category to column D, you need to fine tune the Named Range formulas

e.g. for Actual:

Please Login or Register  to view this content.

and then fine tune the Summary formulas.

e.g. for Actual in D6:

Please Login or Register  to view this content.

and in L6:

Please Login or Register  to view this content.