Indirect summation

[BenElliott]

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
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]2017
[/TD]
[TD]2016
[/TD]
[/TR]
[TR]
[TD]Turnover
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Year 2
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]2018
[/TD]
[TD]2017
[/TD]
[TD]2016
[/TD]
[/TR]
[TR]
[TD]Turnover
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

=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")

. The contents of these cells are

<colgroup><col width="89"></colgroup><tbody>
[TD="width: 89"]7, -158, 97, 913, 312, 203, 1020, 360, 804, 19986, 212, 11543
[/TD]

</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>

1. Is the "Indirect" method the best one to use in stopping the change of abolute references?
2. Is there a feature of using =Indirect that I'm not aware of?

Hope this makes sense.

Thanks,

Ben

 

[steve the fish]

You have a multiplication within your formula. Is that intended?

 

[BenElliott]

Groan

I know I need glasses but that's ridiculous.

Thanks for spotting that one, Steve.

Ben