Suppose I have a value in cell $SheetX.A1 (say then number 42).

Now, I reference this value in another cell, say $SheetY.M5 by setting its content to =$SheetX.A1 (so I see “42” in cell M5 of sheet Y).

How can I offset the reference targeted by M5 in another cell? For example I would like M6 to target the same location M5 is referencing, offset by 6 rows, in other words I would like M6 to show the value of $SheetX.A7 (six rows down from $SheetX.A1) because that is what M5 is referencing. Something like “=OFFSET(TARGET(M5),6)”.

• I know that if I can get a value with =INDIRECT(“addr”)
• I also know that I can get the formula of a cell with =FORMULA(ref), so =FORMULA(M5) will return $SheetX.A1

I suppose I could do some string manipulation on that to remove the equals sign and then amend the trailing number 1 to increment it, somehow generating text “$Sheet.A7” which I can pass to INDIRECT()?

How does one go about doing this? What is the best way?