Prev: Sumif does not calculate beyond 1000 rows
Next: Slow Cut, Copy and Paste in Excel 2007
From: Bob on 19 Mar 2010 15:39
Every month I create a spreadsheet that ranks customers by past due amounts
owed that I share with our sales team. What's important is the relational
change in an account's position on that list, along with how much they owe.
Obviously if you show up on the list at position 50, spend 9 months on the
list and are now at position 3 the situation is more important than someone
who pops up on the list at position 20, drops to position 50 in the course of
3 months!

I want to create a comparison similar to the way they do the Billboard
record sales charts:
* Account's position this month (easy - use RANK)
* Number of months on the top 20 list (Not sure how to do it)
* On current month's listing, show the position in the previous month's
ranking (probably vlookup?) (I'll do conditional formatting to show worsening
accounts in red vs. improving accounts in green)

I'm looking for advice on constructing this type of workbook: new tabs each
month, and rollover formulas? Or a "master" tab with historical data and a
"current" tab that shows results based on historical tab + current month's
data?

Any guidance someone can provide will be most appreciated.
 | 
Pages: 1
Prev: Sumif does not calculate beyond 1000 rows
Next: Slow Cut, Copy and Paste in Excel 2007