refernce cells not being updated when sheet sorted

[jonno2310]

Hi, I have two sheets that some of the cells are linked( referenced). Sheet 1 is a sport ladder, and the second sheet is the teams scoring percentages etc. Whenthe sheet1 is updated and sorted the referenced cells in sheet2 are not updated. This makes a real mess of sheet2.

Microsoft Excel - NBLLAD.XLS ___Running: xl2002 XP : OS = Windows XP

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

E3F3G3E4F4G4E5F5G5E6F6G6E7F7G7E8F8G8E9F9G9E10F10G10E11F11G11E12F12G12E13F13G13 =


A B C D E F G
3 ADELAIDE 36'S 7 1 102.6% 87.5% 8
4 SYDNEY KINGS 6 2 111.0% 75.0% 8
5 MELBOURNE TIGERS 6 3 112.9% 66.7% 9
6 CAIRNS TAIPANS 5 3 109.6% 62.5% 8
7 BRISBANE BULLETS 4 3 105.9% 57.1% 7
8 TOWNSVILLE CROCS 4 6 95.2% 40.0% 10
9 WOLLONGONG HAWKS 3 5 95.9% 37.5% 8
10 W. SYDNEY RAZORS 3 5 95.4% 37.5% 8
11 NEW ZEAL. BREAKERS 3 6 93.4% 33.3% 9
12 PERTH WILDCATS 3 6 97.6% 33.3% 9
13 HUNTER PIRATES 2 6 86.7% 25.0% 8
Ladder

That is sheet1 called "ladder"

1 TEAM Pts + Pts - Pts % Pts+Av Pts-Av
2 SYDNEY KINGS 835 752 111.04% 104.38 94.00
3 ADELAIDE 36'S 816 795 102.64% 102.00 99.38
4 PERTH WILDCATS 868 889 97.64% 96.44 98.78
5 BRISBANE BULLETS 722 682 105.87% 103.14 97.43
6 TOWNSVILLE CROCS 1021 1073 95.15% 102.10 107.30
7 WOLLONGONG HAWKS 729 760 95.92% 91.13 95.00
8 MELBOURNE TIGERS 886 785 112.87% 98.44 87.22
9 HUNTER PIRATES 730 842 86.70% 91.25 105.25
10 CAIRNS TAIPANS 846 772 109.59% 105.75 96.50
11 WEST SYDNEY R-BACKS 810 849 95.41% 101.25 106.13
12 NEW ZEALAND BREAKERS 905 969 93.40% 100.56 107.67
Total Team Pts %

This is sheet2 called Total Team Pts %


I hope someone can answer the problem

Many thanks


Jonno2310

 

[jonno2310]

Doesnt look like the html conversion has worked properley
Hope it can be understood


jonno2310

 

[jonno2310]

will attempt again to put html into message

Jonno2310

NBLLAD.XLS
	A	B	C	D	E	F	G
2			WON	LOST	PTS%	WIN%	GAMES
3	ADELAIDE 36'S		7	1	102.6%	87.5%	8
4	SYDNEY KINGS		6	2	111.0%	75.0%	8
5	MELBOURNE TIGERS		6	3	112.9%	66.7%	9
6	CAIRNS TAIPANS		5	3	109.6%	62.5%	8
7	BRISBANE BULLETS		4	3	105.9%	57.1%	7
8	TOWNSVILLE CROCS		4	6	95.2%	40.0%	10
9	WOLLONGONG HAWKS		3	5	95.9%	37.5%	8
10	W. SYDNEY RAZORS		3	5	95.4%	37.5%	8
11	NEW ZEAL. BREAKERS		3	6	93.4%	33.3%	9
12	PERTH WILDCATS		3	6	97.6%	33.3%	9
13	HUNTER PIRATES		2	6	86.7%	25.0%	8
Ladder

That was sheet 1

*removed by Admin*

 

[Smitty]

Welcome to the Board!

I'm not sure about the HTML Maker issue, you might want to check the HTML Maker FAQ on that one.

As for the other, if you're referring to the first sheet with simple cell ref's, like =A1, you might want to look at INDIRECT in the helpfile, or probably easier, just setup sheet2 with VLOOKUPs.

Post back if you need some examples of either, and someone will post 'em, but I'm outta' here!

Hope that helps,

Smitty

EDIT: OK, something's funky there, at least on my screen, so I reported the post. Maybe one of the brilliant Admin folks can get it sorted.

 

[jonno2310]

I haven't explained the problem sufficiently.

Sheet 1 is a ladder of team performance in descending order, ie. as each round is played and teams win and lose this sheet is manually updated with wins & losses, the order on the ladder and is then sorted. This rearranges the list into the best down to worst performing team.

The number of games palyed by each team is listed in the column 'games' and the cells in this column are referenced in sheet two to determine the averages of 'Points won',average, 'Points against', average.

The problem is when sheet one is sorted the cell references used in sheet two to determine the averages are not updated during the sort. This means that after the sort the cell references used to determine the averages for each team are incorrect and the data in sheet two is then also incorrect.