Cannot Sort: Help Please

[Monte Comeau]

I have created a sheet that calculates the golf scores of 9 man teams. I
have the results returned to a column that I want to sort.

The trouble is when I use the DATA SORT function the results are screwy.

Each cell in the column has a formula similar to this: =G3, to return the
total scores of the team from cell G3

After I sort the column the cells all read an incorrect reference like =G56
and returns a messed up bunch of data.

How can I sort the column and retain the correct data? I just want to sort
the column to determine 1st, 2nd, 3rd etc.

Thanks in advance.

 

[BrianB]

try =$G$3 instead.

 

[Monte Comeau]

Thank you.

Is there a short-cut key to apply this when entering the formula?

 

[Dave Peterson]

If you highlight the address within the formula and hit F4, you'll cycle through
all 4 reference styles:

G3
$g$3
g$3
$g3

If you hightlight the whole formula, then all the references will cycle through
the styles.

 

[Monte Comeau]

Thank you very much for this information.

How does one find out what these $ references do? I search the help files
before posting a question here but I never find what I want. My system came
with Excel preloaded and no manual. I suppose i should invest in a book
know of any good ones?

 

[Dave Peterson]

Debra Dalgleish has a list of books at:
http://www.contextures.com/xlbooks.html

For formulas, John Walkenbach's books get nice reviews.

And the help is nice, too.

Search for reference style and you'll see this
Switch between relative, absolute, and mixed references
(taken from xl2002's help)

And make sure you click on Show All. (Some of the stuff is hidden in there.)

But these references make a big difference when you copy the formula cell to
another location.

put 1,2,3,4,5 in A1:A5
put a,b,c,d,e in B1:B5

Put =$a$1 in C1
drag it down to C5
and drag C1:C5 to D1:d5 (or copy|paste)
notice the results and check each formula.

Now change it to =$a1 (still in C1)
drag it down to C5
and drag C1:C5 to D1:d5 (or copy|paste)
notice the result and look at the formulas to see how they adjusted.

Do it for each style.

You'll get the hang of it.

Thank you very much for this information.

How does one find out what these $ references do? I search the help files
before posting a question here but I never find what I want. My system came
with Excel preloaded and no manual. I suppose i should invest in a book
know of any good ones?

 

[Monte Comeau]

MUCH OBLIGED FOR THE DIRECTIONS!

Debra Dalgleish has a list of books at:
http://www.contextures.com/xlbooks.html

For formulas, John Walkenbach's books get nice reviews.

And the help is nice, too.

Search for reference style and you'll see this
Switch between relative, absolute, and mixed references
(taken from xl2002's help)

And make sure you click on Show All. (Some of the stuff is hidden in there.)

But these references make a big difference when you copy the formula cell to
another location.

put 1,2,3,4,5 in A1:A5
put a,b,c,d,e in B1:B5

Put =$a$1 in C1
drag it down to C5
and drag C1:C5 to D1:d5 (or copy|paste)
notice the results and check each formula.

Now change it to =$a1 (still in C1)
drag it down to C5
and drag C1:C5 to D1:d5 (or copy|paste)
notice the result and look at the formulas to see how they adjusted.

Do it for each style.

You'll get the hang of it.