Forum Discussion
Summary Sheet Problems
So, I have an Excel 365 workbook with multiple sheets. I use it to track monies banked into a single bank account but for multiple purposes (rent, comms, clothing etc). Each sheet has a balance column that, until now, I have manually extended as required, and with a grand total as the last cell. I learnt today the art of using a table that auto-grows (yay !).
I also have a summary sheet that grabs the grand total cell of each sheet and displays this row-by-row (1 x sheet per row with the grand total of that sheet in the next cell). Until now, I have changed the reference cell in the summary sheet each time I have extended the range.
So here's my question: How do I achieve the Summary sheet if I convert all other sheets to tables. Help appreciated please
3 Replies
- Let say you have Jan, Feb and March sheets with the following values in cells A1: 50, 60, 30 formatted in Excel Table using
In your Summary sheet, excuse: =SUM(
2. Click on cell A1 in Jan sheet
3. Hold down the SHIFT key and click on March sheet
4. Click Enter
All the values in cells A1 across the 3 sheets will be summed and delivered in cell A1 of your Summary sheet in structured reference.
So when new values are added to the Tables in any of the three sheets, the table grows.. And this helps you to reference more values across different sheets than using only = sign Lanmeister Without having seen your sheets I would recommend to put all your transactions together in one single sheet and add a column to indicate the purpose of the transaction. Then you will have the possibility to create sort, summarise, filter or create reports from that single data base, using PivotTables SUMIF formulae, filters, sub-totals etc.
Simply:
Type =.
Go to to the cell with the value and select the cell.
Press ENTER.
It should be something like:
=Table1[[#Totals],[Spending]]
