Excel won't sort the way you want when you have formulas like that in your data.

I'd try a different formula--but that formula will depend on the other data.

Maybe you could use:
=vlookup(a2,sheet2!a:d,2,false)
If you have a table with all the wins/losses in one spot.

Or if you have a giant worksheet with Names in column A and W/L/T's in column B:

=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!b1:b100="W"))
similarly for L's
=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!b1:b100="L"))

(extend the range to what you need, but don't use the whole column.)

but that's just a guess based on the other data.

Ps. Since each player will "play" the same number of games, you can figure
games behind just by subtracting wins

=max($B$2:$B$4)-B2
(dragged down.)





stacy wrote:

Hey everyone...

I have a simple football tracking spreadsheet that keeps track of 3
players and their winning percentage. It is basically the number of
wins, number of losses, and games back.

Player Wins Losses Games Back
A 33 27 0
B 31 29 2
C 29 31 4

My problem is, the numbers for wins and losses are cell references to
another part of the spreadsheet. Basically, '33' is really =F18, and
'27' is =F19, and so on. The formula I am using to calculate games
back is as follows:

=(((D26+E26)-($D$26+$E$26))*0.5)+($D$26-D26)

However, this formula is dependent on the data to be sorted by always
having the highest number of wins player listed first, hence my sorting
issue. Whenever I sort the data in the grid above, using the row
containing wins as the control, it flips out and does some strange
things... Changing numbers and references.

Any ideas on how to solve this (simple I am sure) problem would be
great. Thanks as always ahead of time!!!

--

Dave Peterson