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?

  • 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 Mar 18 '19 at 19:24
  • sorry, whats the more elegant solution to which you refer? – Bjorks number one fan Mar 18 '19 at 19:25
  • I don't make a reference to a more elegant solution... – cybernetic.nomad Mar 18 '19 at 19:41
  • 1
    You may want to explain what exactly you are trying to do as I think we are approaching XY problem territory – cybernetic.nomad Mar 18 '19 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. Mar 19 '19 at 5:03
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.