Excel - Reference a column for a formula, using in

Question

I am trying to further my knowledge in excel, I have searched around all day for an answer to my current question but I was unable to find anything relating to my needs.

I basically, want to know if it is possible to reference which column a formula should use, by storing it in a cell.

So if I have a formula which is using column - Test!C:C, am I able to store that string in a cell and reference that cell in the formula? I have many formulas which are using the same reference, so if i decide to change what column i want to utilize, it takes some time to remove them all.

I know i can use replace all, but it would be fantastic if there was a method to reference a column via a cell.

Answer 1

Use the Indirect Function:

If the cell in which you put the column Address "Test!C:C" was A1, you would use:

=INDIRECT(A1)

If the sheet never changed and you only wanted to reference the column. So in A1 you only want to put "C":

=INDIRECT("Test!" & A1 & ":" & A1)

Indirect lets you enter a string that is then used as a reference.

There is one major draw back to the INDIRECT() function, it is volatile. This means that it will calculate every time excel calculates, not only when the reference cells change. So depending on the number of formulas, it will slow the calculation times.