software

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 quot;lock inquot; 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
lt;Insertgt;lt;Namegt;lt;Definegt;
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:

www.contextures.com/xlNames01.html#DynamicDoes that help?

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

XL2002, WinXP-Proquot;amyquot; wrote:

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

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

Thanks
Amy

quot;Ron Coderrequot; wrote:

gt; You seem to have a couple unnecessary complications working against you.
gt;
gt; First, you are using Relative References instead of Absolute References in
gt; your base formula.
gt;
gt; Instead of this: =RANK(C6,C6:C44)
gt; Use this: =RANK(C6,$C$6:$C$44)
gt; When you copy that formula down...the C6 will change to C7, C8, etc...but
gt; the $C$6:$C$44 part will remain constant. The dollar signs quot;lock inquot; that
gt; part of the reference.
gt;
gt; Second, you might want to consider using either a Named Range or a Dynamic
gt; Range Name in your formula instead of $C$6:$C$44.
gt;
gt; Named Range Example:
gt; Select $C$6:$C$44
gt; lt;Insertgt;lt;Namegt;lt;Definegt;
gt; Names in Workbook: SalesData
gt; Refers to: (already selected: $C$6:$C$44)
gt; Click the [OK] button
gt;
gt; Now your formula can be: =RANK(C6,SalesData)
gt; Plus...if there are more, or less, salespeople you can just redefine the
gt; referenced range one time and all of the formulas will calculate properly.
gt;
gt; A Dynamic Range Name works like a regular Named Range, but it resizes itself
gt; automatically based on the number of cells will values. If you're interested
gt; in them, see Debra Dalgleish's Contextures website for instructions:
gt;
gt; www.contextures.com/xlNames01.html#Dynamic
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;amyquot; wrote:
gt;
gt; gt; I use an existing spreadsheet every month to calculate salespeoples sales.
gt; gt; With turn over the number of sales people changes every month. Which means
gt; gt; the rows I am trying to rank change every month. I takes me hours to change
gt; gt; =RANK(C6,C6:C44) to say =RANK(C6, C6:C50) on every cell since the copy paste
gt; gt; function distorts the formula. Is there any way I can say sort all of row C
gt; gt; no matter how much data is there? Please help....
gt; gt;
gt; gt; Thank you in advance!
gt; gt;

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

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

XL2002, WinXP-Proquot;amyquot; wrote:

gt; Ron thank you for your help and the simplistic way you wrote it! You saved me
gt; hours!
gt;
gt; Thanks
gt; Amy
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; You seem to have a couple unnecessary complications working against you.
gt; gt;
gt; gt; First, you are using Relative References instead of Absolute References in
gt; gt; your base formula.
gt; gt;
gt; gt; Instead of this: =RANK(C6,C6:C44)
gt; gt; Use this: =RANK(C6,$C$6:$C$44)
gt; gt; When you copy that formula down...the C6 will change to C7, C8, etc...but
gt; gt; the $C$6:$C$44 part will remain constant. The dollar signs quot;lock inquot; that
gt; gt; part of the reference.
gt; gt;
gt; gt; Second, you might want to consider using either a Named Range or a Dynamic
gt; gt; Range Name in your formula instead of $C$6:$C$44.
gt; gt;
gt; gt; Named Range Example:
gt; gt; Select $C$6:$C$44
gt; gt; lt;Insertgt;lt;Namegt;lt;Definegt;
gt; gt; Names in Workbook: SalesData
gt; gt; Refers to: (already selected: $C$6:$C$44)
gt; gt; Click the [OK] button
gt; gt;
gt; gt; Now your formula can be: =RANK(C6,SalesData)
gt; gt; Plus...if there are more, or less, salespeople you can just redefine the
gt; gt; referenced range one time and all of the formulas will calculate properly.
gt; gt;
gt; gt; A Dynamic Range Name works like a regular Named Range, but it resizes itself
gt; gt; automatically based on the number of cells will values. If you're interested
gt; gt; in them, see Debra Dalgleish's Contextures website for instructions:
gt; gt;
gt; gt; www.contextures.com/xlNames01.html#Dynamic
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;amyquot; wrote:
gt; gt;
gt; gt; gt; I use an existing spreadsheet every month to calculate salespeoples sales.
gt; gt; gt; With turn over the number of sales people changes every month. Which means
gt; gt; gt; the rows I am trying to rank change every month. I takes me hours to change
gt; gt; gt; =RANK(C6,C6:C44) to say =RANK(C6, C6:C50) on every cell since the copy paste
gt; gt; gt; function distorts the formula. Is there any way I can say sort all of row C
gt; gt; gt; no matter how much data is there? Please help....
gt; gt; gt;
gt; gt; gt; Thank you in advance!
gt; gt; gt;