BenElliott
Board Regular
- Joined
- Jul 19, 2012
- Messages
- 144
Hello, all.
I can't find this question referenced in the forum before.
I have a sheet where Column 'A' always remains the same each year I use it. But I always add columns between column 'A' and 'C' each year, like this:
Year 1
<tbody>
</tbody>Year 2
<tbody>
</tbody>So, everything moves across one column each year. The current year's figures are always in column 'B'
I have a small table elsewhere on the sheet that sumarises what is in Column 'B'. My problem is that by adding a column, the formulae in this table, even when absolute references (e.g. $B$2) adjusts to counter the adding of the column.
To get round this I have started using the =Indirect() instruction and by-and-large it works. But, I have one cell that refuses to give the correct answer.
the formula in this cell is
. The contents of these cells are
<colgroup><col width="89"></colgroup><tbody>
</tbody>and their total should be 35,379 but my formula, above gives the result as 63,739 and for the life of me, I cannot see why.
So, my question is in two parts>
Thanks,
Ben
I can't find this question referenced in the forum before.
I have a sheet where Column 'A' always remains the same each year I use it. But I always add columns between column 'A' and 'C' each year, like this:
Year 1
2017 | 2016 | |
Turnover |
<tbody>
</tbody>
2018 | 2017 | 2016 | |
Turnover |
<tbody>
</tbody>
I have a small table elsewhere on the sheet that sumarises what is in Column 'B'. My problem is that by adding a column, the formulae in this table, even when absolute references (e.g. $B$2) adjusts to counter the adding of the column.
To get round this I have started using the =Indirect() instruction and by-and-large it works. But, I have one cell that refuses to give the correct answer.
the formula in this cell is
Code:
=INDIRECT("B11")+INDIRECT("B22")+INDIRECT("B23")+INDIRECT("B24")+INDIRECT("B25")+INDIRECT("B28")+INDIRECT("B30")*INDIRECT("B32")+INDIRECT("B35")+INDIRECT("B41")+INDIRECT("B42")+INDIRECT("B43")+INDIRECT("B29")
7, -158, 97, 913, 312, 203, 1020, 360, 804, 19986, 212, 11543 |
<colgroup><col width="89"></colgroup><tbody>
</tbody>
So, my question is in two parts>
- Is the "Indirect" method the best one to use in stopping the change of abolute references?
- Is there a feature of using =Indirect that I'm not aware of?
Thanks,
Ben