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 |
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. |
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 |
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 |
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 |
All times are GMT +1. The time now is 12:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com