Hello - I suffer from ADHD and even with medication, I have a hard time thinking through complex problems like this. I'm hoping this is easier for someone than it has been for me. Thank you in advance for your assistance!
I would like to sort a list of items automatically and continuously. Each item and its associated information is listed in a row. We are taking a cumulative rank of these two columns and then further ranking by proximity to due date. If past due date, then ranking should be higher than those with due dates in the future. The spreadsheet should automatically sort / rank based on today's date.
The items themselves have the following criteria (each in a separate cell column): Name (irrelevant), A Value to which this formula / macro would assign a ranking of 1-4 (column uses actual words - Highest = 1, High = 2, Medium = 3, Low = 4), Values 1-5 (Also using names, same as previous), Today's Date, Due Date.
So each row would contain columns which look like this:
Name | Highest | Driver | 3/20/12 | 3/25/12 | RANK?
Name | Low | Milk | 3/19/12 | 3/18/12 | RANK?
Name | High | Blah | 3/20/12 | 6/03/12 | RANK?
I need the fifth column (In example above, "Rank?") to return a ranking for each row (1-3 in this example, but for every row in my spreadsheet) by taking the the rank of column 2 (1= Highest, 4= Lowest) plus column 3 (so that a rank of 1 in first column and 1 in second column produces the highest ranking), then the difference (number of actual days) between columns 4 and 5 as the third factor in the rank (so that days past due date is highest with lowest number of days to due date following.)
Please let me know what needs further clarification. Thanks again.
I would like to sort a list of items automatically and continuously. Each item and its associated information is listed in a row. We are taking a cumulative rank of these two columns and then further ranking by proximity to due date. If past due date, then ranking should be higher than those with due dates in the future. The spreadsheet should automatically sort / rank based on today's date.
The items themselves have the following criteria (each in a separate cell column): Name (irrelevant), A Value to which this formula / macro would assign a ranking of 1-4 (column uses actual words - Highest = 1, High = 2, Medium = 3, Low = 4), Values 1-5 (Also using names, same as previous), Today's Date, Due Date.
So each row would contain columns which look like this:
Name | Highest | Driver | 3/20/12 | 3/25/12 | RANK?
Name | Low | Milk | 3/19/12 | 3/18/12 | RANK?
Name | High | Blah | 3/20/12 | 6/03/12 | RANK?
I need the fifth column (In example above, "Rank?") to return a ranking for each row (1-3 in this example, but for every row in my spreadsheet) by taking the the rank of column 2 (1= Highest, 4= Lowest) plus column 3 (so that a rank of 1 in first column and 1 in second column produces the highest ranking), then the difference (number of actual days) between columns 4 and 5 as the third factor in the rank (so that days past due date is highest with lowest number of days to due date following.)
Please let me know what needs further clarification. Thanks again.
