I inherited some pretty funky spreadsheets for a client of mine. One sheet is a summary of certain other worksheets (same spreadsheet), where the number being transferred to the summary moves. Think of taking Gross Sales from a Income Statement spreadsheet, Each month the cell changes from B5 to C5...J5 and so on. The Summary statement takes a large number of these values (different rows). The only way I can figure is changing each cell in the Summary sheet (editing formula and change "B" to "C" and so on). Is there an easier way - this is maddening and really a time waster?
For our Excel Guru's, fixing formulas that change from underlying sheets
Do I understand correctly that your detail worksheet contains multiple periods (each in a different column) and you want your summary worksheet to summarize only a specific period?
If so, you can enter a date field on the summary sheet and update it each time you want the data to update. Then, in the formulas under the summary tab, use the SUMIF function to sum the data in the column on the detail worksheet where the date on the summary worksheet matches the date in the header row of the detail worksheet.
I hope that helps!
The best approach would be to improve the layout of your reports. But that's a long-term project. (I can describe an improved approach if you ask.)
But in the short term...
...If you use
...You could use the OFFSET(ref, row_offset, column_offset, height, width) function to reference the current column. To illustrate from your example:
---a reference to B5 in January (with MonthNum =1)
---a reference to C5 in February (with MonthNum =2)
---etc., where MonthNum is a defined name in your workbook.
I have used the above technique very successfully.
You can also consider HLOOKUP.
Another technique is to insert the rows and columns on the non-Summary tab so the formula on the Summary tab moves automatically.
If none of these work, reach out to me privately. Let me access the file remotely so I can see it and suggest something for you. If it takes more than 15 minutes, I'll eat my hat.
A spreadsheet speaks 1,024 words.
This is an example. Three columns and just one row of data. I'll just give you the formulas...
Actual Forecast Variance
=+BS!AX19 =+'S1'!L11 =+DF12-DG12
If you copied the 3 cells to the right for next date group,etc, you get:
=+BS!BA19 =+'S1'!O11 =+DI12-DJ12
But should be:
=+BS!AY19 =+'S1'!M11 =+DI12-DJ12
Thus requiring one to modify for each line two cells (in this example). Multiply this by 15 lines in just this single spreadsheet... and it gets very very time consuming.
This make more sense?
Can you insert two blank columns in between the existing columns on the BS and S1 worksheets? That would result in the correct data when you copy the formulas. You could hide the blank columns so that they won't be visible.
I'll look into all these great answers... thanks..