Forum Discussion
Sum cells across multiple changing sheets
Can someone tell me how I can create a summary page across multiple sheets that will have changing totals? I know how to set the summary with set numbers but as I update the totals on the sheets, is there a way to do that without me having to change the setting each time I add a new entry that will change the total?
Candace1 If the location of a cell moves due to inserting or deleting rows or columns excel will automatically update the referring cells. If the cells on those other sheets aren't all in the same location you can manually select each cell on each sheet. But I'm guessing you have a problem beyond that so what about finding a fixed cell on each sheet that won't change and have it reflect/reference the cell of interest on that sheet?
4 Replies
Candace1 I don't know if I completely understand but let's say you have sheets for every month (Jan:Dec) and in cell z100 is some net total on each of those sheets. You can then have a 13th sheet (Totals) and you can sum across all those month sheets using =Sum('Jan:Dec'!Z100)
FYI you can also enter that formula by typing "=sum(" and then selecting the cell on the Jan sheet and then shift clicking the Dec sheet to highlight all the sheets in between.
FYI2 you can also CHANGE a value or formula on multiple sheets simultaneously by highlighting multiple sheets and then typing in a particular cell. For example if you highlight sheets Jan:Dec and then type in cell Z100 something like =sum(A1:Y99) then every month sheet will have that formula entered into cell Z100. USE CAREFULLY as you can easily overwrite data this way
mtarlerthanks for the suggestions. The 3rd option may work for me. To explain it a little more thoroughly, this spreadsheet has different companies on the sheets and will have updated totals as those companies purchase more items. I want updated totals on the summary but the specific cells with the totals will change as I add more line items.
Candace1 If the location of a cell moves due to inserting or deleting rows or columns excel will automatically update the referring cells. If the cells on those other sheets aren't all in the same location you can manually select each cell on each sheet. But I'm guessing you have a problem beyond that so what about finding a fixed cell on each sheet that won't change and have it reflect/reference the cell of interest on that sheet?
- Hello,
Just a suggestion. You can combine all the data from the different sheets into a single sheet. Then, you can format the data as Excel Tables and create a PivotTable Report.
When new data is added to the source data, you can easily refresh your PivotTable
