cell reference

[cary1]

Cell U3 has the formula =j1482. I would like cell AA3 to look at cell U3 and return the formula =p1482. That is, in cell AA3, I would like to substitute the column reference "j" for "p" but keep the row reference 1482 shown in U3.

 

[Peter_SSs]

I'm not sure why you wouldn't just put =P1482 in cell AA3 but if you are using Excel 2013 or later, try

=OFFSET(INDIRECT(MID(FORMULATEXT(U3),2,99)),,6)
or
=INDIRECT(MID(SUBSTITUTE(FORMULATEXT(U3),"J","P"),2,99))

Note that both OFFSET and INDIRECT are volatile functions so if you are using a lot of them they may adversely affect your sheet's performance.

 

[ljubo_gr]

Cell AA3=INDIRECT(SUBSTITUTE(FORMULATEXT(U3),"=J","P"))

 

[Peter_SSs]

Some further comments:

If any columns are subsequently inserted or deleted to the left of column J
- The post #3 formula will error
- The second formula in post #2 will return the value from an incorrect cell
- The first formula from post #2 should 'follow' the target cell and still return the same result.

If any columns are subsequently inserted or deleted between column J and column P
- all 3 formulas will return results from different (presumably incorrect) cells.