0

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?

Improve this question
7
  • 1
    You can if you extract the three values that must be passed to the OFFSET function ( D50, 5 and 5) and then use INDIRECT-- it's going to be a long, messy formula and I have to wonder why you'd want to do that to yourself.
    – cybernetic.nomad
    Commented Mar 18, 2019 at 19:24
  • sorry, whats the more elegant solution to which you refer?
    – 3pitt
    Commented Mar 18, 2019 at 19:25
  • I don't make a reference to a more elegant solution...
    – cybernetic.nomad
    Commented Mar 18, 2019 at 19:41
  • 2
    You may want to explain what exactly you are trying to do as I think we are approaching XY problem territory
    – cybernetic.nomad
    Commented Mar 18, 2019 at 19:45
  • 1
    rather than seeking help for the solution you are trying to implement, perhaps share a concise subset of the workbook along with a sample of the desired result. Use images, sample input data that can be copied and a description of the transformation from the provided text sample input to the mocked up desired result image.
    – Ted D.
    Commented Mar 19, 2019 at 5:03

1 Answer 1

Reset to default
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .