I use an existing spreadsheet every month to calculate salespeoples sales.
With turn over the number of sales people changes every month. Which means
the rows I am trying to rank change every month. I takes me hours to change
=RANK(C6,C6:C44) to say =RANK(C6, C6:C50) on every cell since the copy paste
function distorts the formula. Is there any way I can say sort all of row C
no matter how much data is there? Please help....

Thank you in advance!

You seem to have a couple unnecessary complications working against you.

First, you are using Relative References instead of Absolute References in
your base formula.

Instead of this: =RANK(C6,C6:C44)
Use this: =RANK(C6,$C$6:$C$44)
When you copy that formula down...the C6 will change to C7, C8, etc...but
the $C$6:$C$44 part will remain constant. The dollar signs "lock in" that
part of the reference.

Second, you might want to consider using either a Named Range or a Dynamic
Range Name in your formula instead of $C$6:$C$44.

Named Range Example:
Select $C$6:$C$44
<Insert<Name<Define
Names in Workbook: SalesData
Refers to: (already selected: $C$6:$C$44)
Click the [OK] button

Now your formula can be: =RANK(C6,SalesData)
Plus...if there are more, or less, salespeople you can just redefine the
referenced range one time and all of the formulas will calculate properly.

A Dynamic Range Name works like a regular Named Range, but it resizes itself
automatically based on the number of cells will values. If you're interested
in them, see Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlNames01.html#Dynamic


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"amy" wrote:

I use an existing spreadsheet every month to calculate salespeoples sales.
With turn over the number of sales people changes every month. Which means
the rows I am trying to rank change every month. I takes me hours to change
=RANK(C6,C6:C44) to say =RANK(C6, C6:C50) on every cell since the copy paste
function distorts the formula. Is there any way I can say sort all of row C
no matter how much data is there? Please help....

Thank you in advance!

Ron thank you for your help and the simplistic way you wrote it! You saved me
hours!

Thanks
Amy

"Ron Coderre" wrote:

You seem to have a couple unnecessary complications working against you.

First, you are using Relative References instead of Absolute References in
your base formula.

Instead of this: =RANK(C6,C6:C44)
Use this: =RANK(C6,$C$6:$C$44)
When you copy that formula down...the C6 will change to C7, C8, etc...but
the $C$6:$C$44 part will remain constant. The dollar signs "lock in" that
part of the reference.

Second, you might want to consider using either a Named Range or a Dynamic
Range Name in your formula instead of $C$6:$C$44.

Named Range Example:
Select $C$6:$C$44
<Insert<Name<Define
Names in Workbook: SalesData
Refers to: (already selected: $C$6:$C$44)
Click the [OK] button

Now your formula can be: =RANK(C6,SalesData)
Plus...if there are more, or less, salespeople you can just redefine the
referenced range one time and all of the formulas will calculate properly.

A Dynamic Range Name works like a regular Named Range, but it resizes itself
automatically based on the number of cells will values. If you're interested
in them, see Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlNames01.html#Dynamic


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"amy" wrote:

I use an existing spreadsheet every month to calculate salespeoples sales.
With turn over the number of sales people changes every month. Which means
the rows I am trying to rank change every month. I takes me hours to change
=RANK(C6,C6:C44) to say =RANK(C6, C6:C50) on every cell since the copy paste
function distorts the formula. Is there any way I can say sort all of row C
no matter how much data is there? Please help....

Thank you in advance!

Thanks for the feedback, Amy.
I'm glad that worked for you and saved you some time.

***********
Regards,
Ron

XL2002, WinXP-Pro


"amy" wrote:

Ron thank you for your help and the simplistic way you wrote it! You saved me
hours!

Thanks
Amy

"Ron Coderre" wrote:

You seem to have a couple unnecessary complications working against you.

First, you are using Relative References instead of Absolute References in
your base formula.

Instead of this: =RANK(C6,C6:C44)
Use this: =RANK(C6,$C$6:$C$44)
When you copy that formula down...the C6 will change to C7, C8, etc...but
the $C$6:$C$44 part will remain constant. The dollar signs "lock in" that
part of the reference.

Second, you might want to consider using either a Named Range or a Dynamic
Range Name in your formula instead of $C$6:$C$44.

Named Range Example:
Select $C$6:$C$44
<Insert<Name<Define
Names in Workbook: SalesData
Refers to: (already selected: $C$6:$C$44)
Click the [OK] button

Now your formula can be: =RANK(C6,SalesData)
Plus...if there are more, or less, salespeople you can just redefine the
referenced range one time and all of the formulas will calculate properly.

A Dynamic Range Name works like a regular Named Range, but it resizes itself
automatically based on the number of cells will values. If you're interested
in them, see Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlNames01.html#Dynamic


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"amy" wrote:

I use an existing spreadsheet every month to calculate salespeoples sales.
With turn over the number of sales people changes every month. Which means
the rows I am trying to rank change every month. I takes me hours to change
=RANK(C6,C6:C44) to say =RANK(C6, C6:C50) on every cell since the copy paste
function distorts the formula. Is there any way I can say sort all of row C
no matter how much data is there? Please help....

Thank you in advance!