Search code examples
excelexcel-formula

Best way to offset the target reference of another cell?

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?

Solution

  • You can try this formula:

    =LET(start,M5,offsetRows,5,
target,INDIRECT(MID(FORMULATEXT(start),2,LEN(FORMULATEXT(start))-1)),
IF(ISERROR(target),"n.a.",OFFSET(target,offsetRows,0)
))

    It will offset if the INDIRECTof the formula returns a valid result - otherwise it will return n.a. (e.g. if formula in D1 =$SheetX.A1+1)