Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only certain worksheets in Excel
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only certain worksheets in Excel
Exclusively on Excel 2007.
PivotTable, Consolidate, Multiple Page Select (drop down box) No formulas needed: http://www.savefile.com/files/1808910 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only certain worksheets in Excel
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only certain worksheets in Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to save modified Excel 2003 worksheets using Excel 2007 | Excel Discussion (Misc queries) | |||
How use info in Excel shared worksheets to create new worksheets | Excel Worksheet Functions | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
Weird problem with Excel 2000...Worksheets disappearing in a shared Excel file | Excel Discussion (Misc queries) | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) |