I am working with a group of very complicated offset formulas. I need to see which cells they reference (excel visually highlights the cell off of which the offset is based). The offsets are to a large degree, so I can't manually use my mouse or arrow keys to navigate to them. I can use the cell() function to find the answer - by manually pasting the full offset() formula into the second argument of the cell() function. However this is onerous, and I am working with hundreds of such offset formulas, and I dont have time to manually paste all of them.
Manually, if I type cell("address", OFFSET(D50, 5, 5)) it works fine.
I can get the offset formula alone using formulatext(A1). However, the following fails: cell("address", formulatext(A1)).
Even if I use an additional cell to store the result of formulatext(), that still doesn't work.
Is this possible for the cell() function?
1 Answer
Idea : generate offset() function parameter using cell B2,B3&B4, then generate the row & column number, re-find it using A1 as reference.
1st let type in "D50"(text) into B2, 5 into B3, then 5 into B4. let B1 have =ROW(OFFSET(INDIRECT(B2,TRUE),B3, B4)) and C1 have =COLUMN(OFFSET(INDIRECT(B2,TRUE),B3, B4))
then in the cell formula.. change it to =CELL("address",OFFSET($A$1,$B$1-1,$C$1-1)) .
p/s : This will need you to type in the D50, 5, 5 only once.. note that the cell() formula is the same throughout the sheet.
