Column name letter as a function

[slava]

Hi, I will greatly appreciate your help on this question as I've hit a mental block

My question is about the correct syntax to use for my problem. Say, I have a formula in cell A1 on Sheet1
=Sheet2!N2+10

Say, I also have a cell on the same tab, B1, where letter N is generated by another formula. I need to know either:

1) What is the syntax to substitute column letter N in the formula above with a reference to cell B1 on Sheet1
or
2) What is the syntax that I can use to substitute the column letter N in the formula above with the formula below in cell B1?

The letter N is generated by a formula =SUBSTITUTE(SUBSTITUTE(ADDRESS(10,MATCH($C$1,'Sheet2'!$A$8:$Z$8,0)),"$",""),10,"")

Thank you very much for your time!

 

[Osvaldo Palmeiro]

Hi.

1. with "N" in 'B1' you could use:

=INDIRECT("Sheet2!"&B1&"2")+10

2. with 'N2' in 'B1' >> for this you should add &2 to your formula like >> =(your formula)&2 and use

=INDIRECT("Sheet2!"&B1)+10

 

[slava]

Hi Osvaldo, thank you for looking into this.

How will the syntax change if in my A1 cell on Sheet1 I have =SUM('Sheet2'!N19:N21)

I want to substitute letter N in that formula with the formula that produced letter N or with a reference to another cell containing letter N.

 

[Osvaldo Palmeiro]

Hi.
Assuming that the formula in Sheet1 'B1' produces the letter 'N', try this in Sheet1 'A1':

=SUM(INDIRECT("Sheet2!"&B1&"19:"&B1&"21"))