Sum only certain worksheets in Excel

[JLS]

Hello-
I have a file with 8 worksheets, each representing a different location. I
need to create a Total worksheet that will allow the user to select how many
locations they would like to see in the Total.
Ex. User 1 wants to see the Total if all 8 locations are summed.
User 2 is conservative and only wants to see the Total of 5 locations. etc.

Ideally, I envision a drop down box at the top that can be changed by the
User that will in turn change the totals in the spreadsheet automatically.
Any ideas would be greatly appreciated!

 

[NoodNutt]

G'day

Something to try: Assume

A B C D

1 Sheet Select Sheet Formula
2 Name Sheet Total in Column C
3
4 Sheet1 X 1000
=IF(B4="","",Sheet1!$B$1)
5 Sheet2
=IF(B5="","",Sheet2!$B$1)
6 Sheet3
=IF(B6="","",Sheet3!$B$1)
7 Sheet4 X 4000
=IF(B7="","",Sheet4!$B$1)

Total of Selected Sheets 5000 =SUMIF(B4:B7,"X",C4:C7)

Change the cell references to suit.

You could have the user select in two ways:

1. They have to manually type in "X"
2. Create a dropdown list with the "X" in it using data validation/name
range.

I would then consider creating a macro and placing it on the sheet to delete
the selected "X" rather than doing it the manually.

HTH
Mark.

 

[Herbert Seidenberg]

Exclusively on Excel 2007.
PivotTable, Consolidate,
Multiple Page Select (drop down box)
No formulas needed:
http://www.savefile.com/files/1808910

 

[ShaneDevenshire]

Hi,

we might be more help is you could explain what the spreadsheet names are
and how you would know when a user want to sum 5 locations, which 5? The
1st, 2nd, 3rd, 4th and 5th, or for example, the 1st, 3rd, 5th, 7th, and 8th?

 

[T. Valko]

I understand you want to be able to select what sheets are used in a
calculation *but* you haven't defined what that calculation is or what cells
need to be included.