# Identify Column by Letter in a Cell then use in a Formula

#### rwmill9716

##### Active Member
I have a column of numbers in the A column (columns may change from week to week) say rows 10 to 110. I want to identify that column by specifying the column name (A) in a cell (say AA1). I then want to incorporate that name in a formula to copy the results, i.e., AA10=A10, AA11=A11 ... AA110=A110. The next week this data column may be in Column B (specified in Cell AA1) so my formula become AA10=B10, AA11=B11 and so on. This seems trivial and easily done with a cut and paste operation, but I have several columns that need to be copied, and being able to just identify the column to be copied for existing formula would save a lot of work.

Ric

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Joe4

Are you just trying to dynamically build range references in a formula?
If so, you can use the INDIRECT function to do that.
See: INDIRECT Function

#### rwmill9716

##### Active Member
Thanks, Joe4,

I have used the INDIRECT function to set row values as is being done here:

=SUM(INDIRECT("A"&B1&":A"&C1)) The argument to the INDIRECT function is "A"&B1&":A"&C1 and B1 and C1 have numeric values.

What I need is to set the column values, i.e., my rows will always be 1 to 100, my columns will vary from A to M. I need to set a "M" in a cell to then specify to copy to the rows in column M.

#### Joe4

There is nothing that prevents using INDIRECT on column references instead of row references.
If you have "M" in cell A1, and you want it to create range M1:M100, simply use:
Code:
``[COLOR=#333333]=SUM(INDIRECT(A1 & "1:" & A1 & "100")) [/COLOR]``

#### rwmill9716

##### Active Member
Thanks, Joe4, that works perfectly. I learned something today.

Ric

#### Joe4

You are welcome.
INDIRECT is pretty flexible. You could even have a situation where you build BOTH the column and row references.

Replies
2
Views
259
Replies
3
Views
972
Replies
9
Views
468
Replies
0
Views
361
Replies
4
Views
2K

1,191,003
Messages
5,984,109
Members
439,872
Latest member
noaman79

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