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" wrote in message
...
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!