Last cell of data in a column

I have a file with several worksheets, each of which contains a different concert tour schedule. Each schedule is different from the other in terms of number of show dates. Column A contains the individual dates. Cell A1 contains the formula "=TODAY()", and cell A2 contains the very first date of each tour.

What I need to do is write a formula in cell B1 that compares cell A1 (today's date) with the first and last date of each schedule.

- If the first date (cell A2) is a future date, cell B1 should display "Future".
- If the first date has passed but the last date is a future date, cell B1 should display "Current".
- If the last date has passed, cell B1 should display "Completed".

What I don't know how to do (if this is even possible) is come up with a formula that looks to the last cell of data in column A. I can "hard code" it after I input the schedule, but since each schedule is different (one tour may have 25 dates, another only 8), I would have to change the formula each time.

Hopefully I am being clear -- can this be done?

B2:
=IF(A2>A1,"Future",IF(INDEX(A:A,MATCH(9.99999999999999E+307,A:A))>TODAY(),"C
urrent","Completed"))

--

HTH

RP

"LACA" <[email protected]> wrote in message
news:[email protected]...
>
> I have a file with several worksheets, each of which contains a
> different concert tour schedule. Each schedule is different from the
> other in terms of number of show dates. Column A contains the
> individual dates. Cell A1 contains the formula "=TODAY()", and cell A2
> contains the very first date of each tour.
>
> What I need to do is write a formula in cell B1 that compares cell A1
> (today's date) with the first and last date of each schedule.
>
> - If the first date (cell A2) is a future date, cell B1 should display
> "Future".
> - If the first date has passed but the last date is a future date, cell
> B1 should display "Current".
> - If the last date has passed, cell B1 should display "Completed".
>
> What I don't know how to do (if this is even possible) is come up with
> a formula that looks to the last cell of data in column A. I can "hard
> code" it after I input the schedule, but since each schedule is
> different (one tour may have 25 dates, another only 8), I would have to
> change the formula each time.
>
> Hopefully I am being clear -- can this be done?
>
>
> --
> LACA
> ------------------------------------------------------------------------
> LACA's Profile:
http://www.excelforum.com/member.php...o&userid=30381
> View this thread: http://www.excelforum.com/showthread...hreadid=502190
>