Sorting Data that feeds into other formulas....

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!

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>