Forum Discussion
how do i get formulas to work through multple pages?
Good morning. I am currently trying to create a coding sheet. I have it pretty much finished however what i am looking to do is only use specific rows throughout the spreadsheet. I'm sorry if i am terrible about explaining this.
i have 13 pages one for each month and one is the total. All of the rows and colums of each month Add up on the total sheet which is what is needed. However this spreadsheet is going to be project specific and not all of the rows are needed. So at the beginning of a construction project i want to be able to select certain rows and have the rest of the rows that I don't need hide and subsequently hide on the other 12 months.
I hope this makes sense
Thanks in advance.
https://rnfventures-my.sharepoint.com/:x:/p/nathan/EWrCPQ5azLhHjtSfDGzIrf0BzBl0vr6n-dyV5BQfcHQmwA?e=dJVd7l
1 Reply
Nathanw24 Forgive me for being blunt, but I would choose a different approach, altogether. Working with monthly sheets that link to a Total sheet for the year is almost never a good idea.
Create a budget template that contains a structured table with all Codes and Descriptions and Amount to begin with in a Budget sheet and another sheet with an empty table (only the headers Date, Code, Description, Amount).
For a new project, fill in the budgeted amounts for the relevant codes and delete the rows with no amounts. Then, as the project goes on, keep track of actual spending in a single table [Date (enter manually), Code (using data validation), Description (using one of Excel's lookup functions) and Amount (enter manually)]. That's the table in the second sheet.
Assuming you are on a Windows system (i.e. not a Mac), you can summarize the budget and spending tables with Power Pivot (Data Model) in a pivot table resembling your TOTAL sheet. No formulas required. Just enter expenditures and press Refresh All on the Data ribbon to get the an updated view of the financial status of the project.
But, if you insist on continuing with the design you have now and given that all the sheets have exactly the same lay-out, you could enter the budget amounts in the TOTAL sheet. Then press Shift and click on the Dec tab (selecting all tabs) and delete all rows you don't want in the TOTAL sheet. The same rows in all tabs are now deleted as well. Click any tab to unselect all. the formulas in the TOTAL sheet are updated automatically.
