Moving Formulas when Sorting
 

Hi All & Happy Christmas & New year,

How can I sort a table and have the formulas move to the relevant new
postition?

I'm working on a World Cup spreadsheet so I've got each table as follows

a b c d e f g h i
P W D L F A Pts GD
Team A
Team B
Team C
Team D

The fixtures are below the table and this is where each teams results are
from in the table.
EG.
In cell C2 (Team A Won) the formula is =SUM(A11+A15+K21)
In cell D2 (Team A Draw) =SUM(B11+B15+L21)
These formulas should always be looking at these cells.
(The cells refered to in the above formulae will either be 1 or 0).

When I go to sort the table it messes up the formulas & the results that
should be shown.
If Team D moves to 1st place (moves up 3 places) it also adjusts the
formulas for team D & looks in totally wrong cells (3 rows above original
cells) !!!!!!!!

Thanks in advance for all answers & help
John

Moving Formulas when Sorting
 

In what way do you intend that =SUM(A11+A15+K21) should be different from
=A11+A15+K21, or =SUM(B11+B15+L21) to be different from =B11+B15+L21 ?

Perhaps you need to look at Excel help for the SUM function ?
--
David Biddulph

"Originalgoth" wrote in message
...
Hi All & Happy Christmas & New year,

How can I sort a table and have the formulas move to the relevant new
postition?

I'm working on a World Cup spreadsheet so I've got each table as follows

a b c d e f g h i
P W D L F A Pts GD
Team A
Team B
Team C
Team D

The fixtures are below the table and this is where each teams results are
from in the table.
EG.
In cell C2 (Team A Won) the formula is =SUM(A11+A15+K21)
In cell D2 (Team A Draw) =SUM(B11+B15+L21)
These formulas should always be looking at these cells.
(The cells refered to in the above formulae will either be 1 or 0).

When I go to sort the table it messes up the formulas & the results that
should be shown.
If Team D moves to 1st place (moves up 3 places) it also adjusts the
formulas for team D & looks in totally wrong cells (3 rows above original
cells) !!!!!!!!

Thanks in advance for all answers & help
John

Moving Formulas when Sorting
 

As far as I remember - is cases where the formulas are refer to cells OUTSIDE
the sorted range - they should use Absolute Row & Column references (with 2
Dollar symbols)
Micky


"Originalgoth" wrote:

Hi All & Happy Christmas & New year,

How can I sort a table and have the formulas move to the relevant new
postition?

I'm working on a World Cup spreadsheet so I've got each table as follows

a b c d e f g h i
P W D L F A Pts GD
Team A
Team B
Team C
Team D

The fixtures are below the table and this is where each teams results are
from in the table.
EG.
In cell C2 (Team A Won) the formula is =SUM(A11+A15+K21)
In cell D2 (Team A Draw) =SUM(B11+B15+L21)
These formulas should always be looking at these cells.
(The cells refered to in the above formulae will either be 1 or 0).

When I go to sort the table it messes up the formulas & the results that
should be shown.
If Team D moves to 1st place (moves up 3 places) it also adjusts the
formulas for team D & looks in totally wrong cells (3 rows above original
cells) !!!!!!!!

Thanks in advance for all answers & help
John

Moving Formulas when Sorting
 

Thanks Micky,
That works great.
Does exactly what I wanted it to do.

To David,
It didn't really make any difference by ommiting the SUM bit but thanks
anyway.



"מיכאל (מיקי) אבידן" wrote:

As far as I remember - is cases where the formulas are refer to cells OUTSIDE
the sorted range - they should use Absolute Row & Column references (with 2
Dollar symbols)
Micky


"Originalgoth" wrote:

Hi All & Happy Christmas & New year,

How can I sort a table and have the formulas move to the relevant new
postition?

I'm working on a World Cup spreadsheet so I've got each table as follows

a b c d e f g h i
P W D L F A Pts GD
Team A
Team B
Team C
Team D

The fixtures are below the table and this is where each teams results are
from in the table.
EG.
In cell C2 (Team A Won) the formula is =SUM(A11+A15+K21)
In cell D2 (Team A Draw) =SUM(B11+B15+L21)
These formulas should always be looking at these cells.
(The cells refered to in the above formulae will either be 1 or 0).

When I go to sort the table it messes up the formulas & the results that
should be shown.
If Team D moves to 1st place (moves up 3 places) it also adjusts the
formulas for team D & looks in totally wrong cells (3 rows above original
cells) !!!!!!!!

Thanks in advance for all answers & help
John