Difficult problem: Function that considers the contents of several cells

[stixmike]

I'm not sure if this is possible or not, and I will try to explain it as best I can:

I have a spreadsheet that lists 17 procedures in 17 rows (each procedure name starts on C4). Each procedure has a review date (which is in cells K4-Kx). The first two columns of the spreadsheet (A and B) are meant to list the procedures that are within 60 and 90 days of their review date. That is, the first two columns are "policy name" (A column) and "review date" (B column). The idea is if a policy's review date (which are the cells in column K) is less than 60 days away (the current date is listed in cell A1), it would appear in the first two columns of the spreadsheet. Further, if the date was less than 60 days away the text would appear yellow, and if the date was less than 30 days away, the text would appear red).

The point of all of this is that there will eventually be dozens of procedures, and the spreadsheet needs an easy way to tell the user that one of the procedures is up for review. That's why I wanted only the procedures within 60 and 30 days to appear in the first two columns.

The problem I have is that 1) I don't know if it's possible to have a function consider one cell to infinity, so that when more rows are added you don't have to change the formula, and 2) if it's even possible, if there are multiple procedures up for review, for excel to place the if/then statement result in consecutive rows (ie, A4, A5, A6, etc.).

I know this is probably too complicated, and might not even work. But if anyone knows I would really appreciate it. Thanks.

[stixmike]

I realized that it would be easier to understand what I was asking if I showed you the spreadsheet. So attached is an example.

[shg]

Please stix to one thread, mike.

http://www.excelforum.com/excel-gene...ral-cells.html

[stixmike]

I'm sorry, when I tried to add the attachment I accidentally created a duplicate thread.