Sum only certain worksheets in Excel

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!
--
JLS

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.

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

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?

--
Thanks,
Shane Devenshire


"JLS" wrote:

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!
--
JLS

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.

--
Biff
Microsoft Excel MVP


"JLS" wrote in message
...
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!
--
JLS