# Column name letter as a function

#### slava

##### New Member
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!

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Osvaldo Palmeiro

##### Well-known Member
Hi.

1. with "N" in 'B1' you could use:
Code:
``=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
Code:
``=INDIRECT("Sheet2!"&B1)+10``

#### slava

##### New Member
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

##### Well-known Member
Hi.
Assuming that the formula in Sheet1 'B1' produces the letter 'N', try this in Sheet1 'A1':
Code:
``=SUM(INDIRECT("Sheet2!"&B1&"19:"&B1&"21"))``

Replies
2
Views
172
Replies
3
Views
399
Replies
15
Views
862
Replies
0
Views
483
Replies
8
Views
248

1,191,287
Messages
5,985,762
Members
439,981
Latest member
ofori francis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back