refernce cells not being updated when sheet sorted

jonno2310

New Member
Joined
May 31, 2005
Messages
9
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,134
Messages
6,106,141
Members
447,996
Latest member
ANDYADAM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top