Forum Discussion

Copper Contributor

Oct 26, 2022

Solved

Cell reference substitution.

I'm trying to create a cell of text of same or another cells address. If I enter in cell C10 =cell("address",c10) I get as a result $C$10 as expected. But I need the result to be the text C1...

Formulas and Functions

HansVogelaar
Nov 01, 2022
JG_Berson

=ROW(INDIRECT(B32)) returns the row number of the cell whose address is in B32.

=COLUMN(INDIRECT(B32)) returns the column number of the cell whose address is in B32.

Copper Contributor

Oct 27, 2022

That only gives me C10 for any cell the formula is in. Not what I wanted.
Thanks anyway.
jgb

Silver Contributor

Oct 27, 2022

Are you aware that Excel provides a ROW() and a COLUMN() function?

JG_Berson
Copper Contributor
Oct 27, 2022
Yes, but that does not give me the results I need.
Column gives me the column number.
I need the column letter combined with row number.
- Detlef_Lewin
  Silver Contributor
  Oct 27, 2022
  JG_Berson
  I was hinting in combing both functions with ADDRESS() function.
  - JG_Berson
    Copper Contributor
    Nov 01, 2022
    Detlef_Lewin
    Sorry for the late reply. I'm not sure what you mean.
    
    The problem is I have a cell that contains a text string that is a cell location, or the ADDRESS. The address is NOT the location of that cell but one nearby.
    
    Say the cell is E6. It contains the cell ref F5 as a text. From that cell I will reference other nearby cells by using col/row offsets from the cell F5. To do that I need to know what COL cell F5 is in. Row is easy.
    
    In use I will type in (say) cell B32 "F5" as a text string, not a direct cell ref.
    
    I need to calc that "F5" as COL 6 and ROW 5. Then use that in =address() to offset other cells to get the data in E5, F5, G5, etc.
    
    There seems to be no way to calculate the COL number from the data in B32
    
    Any ideas???
    
    jgb

Resources