Having trouble with formula to automaticly enter data

[Brian]

I need to be able to have specific data added to a cell
which identifies what stage a process is at.
The cell that will contain the data is E42 on sheet1.
I have in cells E43:49 the stage data i.e.
For Tender
For Contract
Contract Amendment 1
Contract Amendment 2
Contract Amendment 3
Contract Amendment 4
Next to each of these is a cell for entering the date at
which this process was reached. I have tried using an If
statement with AND and NOT together with isblank to check
if the date cells are blank or not, but am being told by
the system I have too many argunments for the formula.

Can anyone please help me with either code or formula to
get over this one.

Here is my formula so far, it worked when I only had the
first three items, but have now added three more to the
list:
=IF((AND(NOT(ISBLANK(G44)),ISBLANK(G45),ISBLANK
(G46),isblank(g47),isblank(g48,isblank(g49))),E44,IF((AND
(NOT(ISBLANK(G44)),NOT(ISBLANK(G45)),ISBLANK(G46),isblank
(g47),isblank(g48,isblank(g49))),E45,IF((AND(NOT(ISBLANK
(G44)),NOT(ISBLANK(G45)),NOT(ISBLANK(G46),isblank
(g47),isblank(g48,isblank(g49)))),E46,IF((AND(NOT(ISBLANK
(G44)),NOT(ISBLANK(G45)),NOT(ISBLANK(G46)),not(isblank
(g47)),isblank(g48,isblank(g49)))),E46,""))))

Did not manage to get all formula refined to take all new
references so this is to point of error message.

Thank you in advance for your assistance.

Brian

 

[jaf]

Hi Brian,
So your data looks something like...
Date Stage
10/8/2004 For Tender
10/15/2004 For Contract
10/22/2004 Contract Amendment 1
10/29/2004 Contract Amendment 2
11/5/2004 Contract Amendment 3
Contract Amendment 4


You can use a lookup table to get the data.

=VLOOKUP(MAX(a1:a7),a1:b7,2)
a1:b7 is the table range and this finds the highest date in the first column
of the table (including the header row), a1:a7.

 

[Brian]

Hi Jaf

Many Thanks the formula works, can't think why I did not
think of that one anyway. The brain cells are not working
as well as they used to.

Once again Thanks.

Brian