Kittine,

In ='Rep Stack Ranking'!GL$12+'Rep Stack Ranking'!GL$13+'Rep Stack
Ranking'!GL$14+'Rep Stack Ranking'!GL$40
the $ means: Always refer to the row number that follows.
So GL$12 will always refer to row 12, whatever you do with your data.
$GL12 will always refer to column GL and $GL$12 will always refer to cell
GL12.

Try taking the "$" out of all your formulae.
Then, when you sort, the reference will "follow" the data to the new cell.

Henry

"Kittine" <Kittine@discussions.microsoft.com> wrote in message
news:9605E27D-36C8-41D5-B650-0BA5535F21F9@microsoft.com...
> Hello:
>
> I have a workbook (Excel 2003) and I am having trouble with a particular
> sorting issue. On page 2, column GL contains specific data that feeds
> into
> formulas used in Page 1. When I sort my data on page 2 it changes the
> formula totals on page 1.
>
> What I want to do is have the ability to 'lock' the data on page 2 so that
> after I sort it doesn't change the totals in my formulas using the Page 2
> cell references.
>
> For example on Page 1, one of my cells reads the following formula:
>
> ='Rep Stack Ranking'!GL$12+'Rep Stack Ranking'!GL$13+'Rep Stack
> Ranking'!GL$14+'Rep Stack Ranking'!GL$40
>
> Rep Stack Ranking being Page 2.
>
> I have to sort other data on Page 2 to show which sales reps are leading
> in
> % to Quota, but Column GL on that same page has other pertinent
> information
> which feeds into Page 1 and eventually Page 3 of my workbook.
>
> Because I have to sort the Reps based on Stack Ranking (when I enter daily
> sales data reps are sorted by Full Time/Part Time then Store Location then
> Name) it doesn't dynamically update the cell references I have made in my
> formulas on Page 1 - causing part of my overall totals to be incorrect.
>
> One final example - if Rep A is on row 10 on Page 2, but due to sales
> performance once I sort to show % to Quota Rep A's information is now on
> row
> 15. BUT my formula on Page 1 still references Row 10 instead of having
> updated to Row 15.
>
> I hope this makes sense to someone
>
> Have a great day ~ and thanks for any suggestions!
>