For our Excel Guru's, fixing formulas that change from underlying sheets

Wayne,

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!

Jennifer

Wayne,

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 Excel 2007 or above, you could use SUMIFS to summarize your data, using the approach that Jennifer suggested. (SUMIFS gives you room to grow, because unlike SUMIF, it supports multiple criteria.)

...You could use the OFFSET(ref, row_offset, column_offset, height, width) function to reference the current column. To illustrate from your example:
=OFFSET($B$5,0,MonthNum-1,1,1) returns
---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.

Charley

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.

Hi Jennifer,

Not exactly.

This is an example. Three columns and just one row of data. I'll just give you the formulas...

Correct entry:

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.