| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum sheets based on criteria
Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say
I have 30 sheets with data and a sheet with formulas that displays totals. The setup and columns are all the same for the 30 sheets. The names of the sheets start at 1 and go to 30. In the total sheet, I would like to be able to type in a range, say 1 to 7, and have the totals from only those sheets calculated. The totals will be the sum of the whole column. So, it would be the sum total of column E for sheets 1 through 7. I am thinking about entering the range and then clicking a command button that would do the work. Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum sheets based on criteria
If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3) -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say I have 30 sheets with data and a sheet with formulas that displays totals. The setup and columns are all the same for the 30 sheets. The names of the sheets start at 1 and go to 30. In the total sheet, I would like to be able to type in a range, say 1 to 7, and have the totals from only those sheets calculated. The totals will be the sum of the whole column. So, it would be the sum total of column E for sheets 1 through 7. I am thinking about entering the range and then clicking a command button that would do the work. Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum sheets based on criteria
Correct, and that's what I am doing for the total of all sheets, but I am
wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in another cell and then have your formula below change accordingly. I am making this for non-experienced Excel users and I'd like to make it so they don't have to change the formula each time (otherwise your solution is what I would do) Let me know if this helps clarify things and if it's even possible, thanks! "Don Guillett" wrote: If?? you have a total in each sheet in the SAME cell then =sum(sheet1:sheet7!a3) -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say I have 30 sheets with data and a sheet with formulas that displays totals. The setup and columns are all the same for the 30 sheets. The names of the sheets start at 1 and go to 30. In the total sheet, I would like to be able to type in a range, say 1 to 7, and have the totals from only those sheets calculated. The totals will be the sum of the whole column. So, it would be the sum total of column E for sheets 1 through 7. I am thinking about entering the range and then clicking a command button that would do the work. Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum sheets based on criteria
Don, I think your solution will work - I was wondering if we could modify 2
things: 1) Instead of a message box, I would like to place the value in a cell - say J4... 2) Instead of summing only one cell for each sheet, is there a way we can sum the entire column for each sheet? Specifically, instead of summing A3, I would like to sum column K. Please let me know and thanks for your patience and help! "Don Guillett" wrote: No can do, even with indirect. You will have to have the sheets in order by index. Sub sumacrossshts() fs = Sheets(CStr(Range("f3"))).Index ls = Sheets(CStr(Range("f4"))).Index For i = fs To ls 'MsgBox Sheets(i).Name ms = ms + Sheets(i).Range("a3") Next i MsgBox ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Correct, and that's what I am doing for the total of all sheets, but I am wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in another cell and then have your formula below change accordingly. I am making this for non-experienced Excel users and I'd like to make it so they don't have to change the formula each time (otherwise your solution is what I would do) Let me know if this helps clarify things and if it's even possible, thanks! "Don Guillett" wrote: If?? you have a total in each sheet in the SAME cell then =sum(sheet1:sheet7!a3) -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say I have 30 sheets with data and a sheet with formulas that displays totals. The setup and columns are all the same for the 30 sheets. The names of the sheets start at 1 and go to 30. In the total sheet, I would like to be able to type in a range, say 1 to 7, and have the totals from only those sheets calculated. The totals will be the sum of the whole column. So, it would be the sum total of column E for sheets 1 through 7. I am thinking about entering the range and then clicking a command button that would do the work. Any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum sheets based on criteria
Assumes you have a total in the same cell on each sheet, ie:a3
to put in a cell on the active sheet 'MsgBox ms range("a3").value=ms -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Don, I think your solution will work - I was wondering if we could modify 2 things: 1) Instead of a message box, I would like to place the value in a cell - say J4... 2) Instead of summing only one cell for each sheet, is there a way we can sum the entire column for each sheet? Specifically, instead of summing A3, I would like to sum column K. Please let me know and thanks for your patience and help! "Don Guillett" wrote: No can do, even with indirect. You will have to have the sheets in order by index. Sub sumacrossshts() fs = Sheets(CStr(Range("f3"))).Index ls = Sheets(CStr(Range("f4"))).Index For i = fs To ls 'MsgBox Sheets(i).Name ms = ms + Sheets(i).Range("a3") Next i MsgBox ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Correct, and that's what I am doing for the total of all sheets, but I am wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in another cell and then have your formula below change accordingly. I am making this for non-experienced Excel users and I'd like to make it so they don't have to change the formula each time (otherwise your solution is what I would do) Let me know if this helps clarify things and if it's even possible, thanks! "Don Guillett" wrote: If?? you have a total in each sheet in the SAME cell then =sum(sheet1:sheet7!a3) -- Don Guillett Microsoft MVP Excel SalesAid Software "npop03" wrote in message ... Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say I have 30 sheets with data and a sheet with formulas that displays totals. The setup and columns are all the same for the 30 sheets. The names of the sheets start at 1 and go to 30. In the total sheet, I would like to be able to type in a range, say 1 to 7, and have the totals from only those sheets calculated. The totals will be the sum of the whole column. So, it would be the sum total of column E for sheets 1 through 7. I am thinking about entering the range and then clicking a command button that would do the work. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
| Min/Max based on criteria (2 sheets) | Excel Worksheet Functions | |||
| Using VBA select sll sheets based on Criteria on each sheet. | Excel Discussion (Misc queries) | |||
| Selecting a criteria range over two sheets? | Excel Discussion (Misc queries) | |||
| populating sheets based on data from parent sheets | Excel Discussion (Misc queries) | |||
Linear Mode
