Linked formula changes when column inserted I have a workbook, where the current

Question

Linked formula changes when column inserted

I have a workbook, where the current month is inserted into column B of sheet 1. Prior months shift over one column when the new one is added.

Sheet 2 has formulas to calculate the percent change from the current month (col B) with one month back (col C), three mo (col E), and six mo (col H).

When the new month is inserted, the formulas on Sheet 2 automatically shift so that it is using col C comparing to D,F,&I.

I don't want Sheet 2 formulas to shift. I want them to always use the data in the real current month (col B).

Can this be done? If not, is there a better way to set this up? Thank you!

Explanation / Answer

GHi.,

Try this in cell A2 of sheet2 and copy down

=INDIRECT("'sheet1'!B"&ROW())/INDIRECT("'sheet1'!C"&ROW())-1

O(r:

The formula (for growth over previous month) in cell A2 of sheet2 should be:

=INDIRECT("'sheet1'!B2")/INDIRECT("'sheet1'!C2")-1

Hope this helps.

If my reply helped, please vote it as Answer.

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.