Forum Discussion
Using the number in a cell as the number of columns added in a formula.
Help!
Previously, Peter Bartholomew helped me solve this problem:
I have a table to which I need to keep adding rows.
At the end of the table, I have several cells taking the values of the last row, and it makes me change the row number in then all every time I add a row.
It would help me a lot if I could do this:
Let's say I want the value of F3 in E8.
Instead of writing =F3 in E8, I want to put the the row number 3 in D4 and write a formula in E8 that takes the column F and the row number 3 I wrote in D4.
He gave me two ways; I used this one:
The function that performs the task you describe is
=INDIRECT("F"&D4)
I now have a different problem related to the previous one:
In the same sheet, when I add columns, I have to modify all formulas again looking for the proper column name, so I wish I could use a formula based on the number N of the columns added that gives me the exact column name
eviera49 Did you try the formula I gave you? The problem you are having with INDIRECT("EL"&A3) is that Excel doesn't know what "EL" is and therefore when you insert columns it doesn't touch it. But if you use =INDEX(EL:EL,A3) then when you insert columns before EL it will automatically update the formula accordingly. If you insert 2 columns it will change EL:EL to EN:EN. I think this is what you need, but if not maybe I'm not understanding.
5 Replies
eviera49 I'm not sure I understand the problem. Is the column that you want always X number of columns away from the active cell or is it always "column F" but column F moves due to additional columns inserted? There are numerous options and some will depend on the above question. Here is 1 option that should work in most cases:
=INDEX(F:F,D4)Thanks for your interest in helping me.
Lets say I have this formula in a cell =INDIRECT("EL"&A3)
The column is EL and the row number is stored in the cell A3.
If I insert a number N of columns before column EL, the right column name will shift and I have to look for it, and modify the formula. The hard part is that I have to modify the formulas in many other cells.
I wish I had a formula that will allow me to modify the previous one in such a way that it calculates the right new column name based on the numbers N of columns I inserted.
And if possible, to be able to just store the number N in a cell and have the formula access the number as it does with the column number.
eviera49 Did you try the formula I gave you? The problem you are having with INDIRECT("EL"&A3) is that Excel doesn't know what "EL" is and therefore when you insert columns it doesn't touch it. But if you use =INDEX(EL:EL,A3) then when you insert columns before EL it will automatically update the formula accordingly. If you insert 2 columns it will change EL:EL to EN:EN. I think this is what you need, but if not maybe I'm not understanding.
