Re-ordering cells changes the formula

[flimper]

Hi,

I'm new so please bear with me. I have 3 rows of information being totalled up c2:c26, g2:g26 and j2:j26.

When I move the cells about the formulas which total the cells (=SUM$C$2:$C$26 etc) change.

For instance if I move C3 above C2 the totals at the bottom change to c3:c26 instead of c2:c26.

If anybody can understand my ramblings I would much appreciate any help. Thanks.

[Carim]

Hi,

Remove absolute references ...

=SUM(C2:C26)

HTH
Carim

[flimper]

Unfortunately it doesn't make any difference.

I cut cells a3 to d3 and insert above a2 to d2 and it changes the formula from c2:c26 to c3:c26

[Carim]

Can you tell us what you are after ...
What is the final result you are looking for ...?

Carim

[flimper]

I want the totals of cells c2:c26 to always be totalled up in cell 27 and not to be affected by me reordering or moving any of the said cells.

[michaelc]

Hi there,

The best I can suggest is that you add in a blank line above row 2 and below row 26 and then expand your formula to include these. i.e, now you have c2:c26. by adding the two lines, you will have c2:c28 with your first line of data being on row three. This way, you can move all lines up and down without affecting the formula as long as you don't go "outside" the new boundaries. You will be able to move c4 to c3 and the total will remain correct (as long as you place c3 where c4 was of course!). Otherwise, use the row insert function to insert new rows before moving items around.

Hope that helps.

MC

[flimper]

I already have a line of text in row 1 so I changed the formula from c2:c26 to c1:c26 and it seems to have solved the problem. Many thanks fior the advice