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

[rwmill9716]

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.

Thanks for your help,

Ric

 

[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]

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:

[COLOR=#333333]=SUM(INDIRECT(A1 & "1:" & A1 & "100")) [/COLOR]

 

[rwmill9716]

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.