Rank a changing row

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

Thanks
Amy

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

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!

 

[Guest]

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

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

XL2002, WinXP-Pro

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

Thanks
Amy

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

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!