more-arw search

Q&A Forum

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

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?

Answers

Jennifer Eversole
Title: Partner & Knowledge Enthusiast
Company: Management Stack, LLC
LinkedIn Profile
(Partner & Knowledge Enthusiast, Management Stack, LLC) |

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

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

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

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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.

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

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?

Jennifer Eversole
Title: Partner & Knowledge Enthusiast
Company: Management Stack, LLC
LinkedIn Profile
(Partner & Knowledge Enthusiast, Management Stack, LLC) |

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.

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

I'll look into all these great answers... thanks..

1090 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.