Sum totals from different sheets where inserting new columns will update formulas

I’m building a financial workbook with 4 different sheets.  The first 3 sheets are data sheets of the Profit & Loss reports for three different branches; City1, City2, and City3.  Each report has sales, Cost of Goods Sold,  and expense categories. 

The 4th sheet is a subtotal sheet showing the totals for all three of the branches.  It’s a fairly simple procedure to just say =’City1!’A2+’City2!”A2+’City3!A2 to get the total sales for all three branches.  Where my issue begins is with the fact that the data sheets are ever growing.  Each month, I will need to add a new column to each of the data sheets for the new month’s financial report.  With the current formula, the formula on the subtotal page moves from A2 to B2 even if I change it to an absolute cell reference by using $’s.  Is it possible to us an Indiredt function here?  I can’t seem to figure out how to add indirectly referenced cells on multiple sheets. 

Any help would be greatly appreciated!   

By: Matthew

Leave a Reply

Your email address will not be published. Required fields are marked *