Forum Discussion
Using the number in a cell as the number of columns added in a formula.
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.
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.