Having trouble with formula to automaticly enter data
 

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(I SBLANK
(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(isb lank
(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

Having trouble with formula to automaticly enter data
 

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.



--

John

johnf202 at hotmail dot com


"Brian" wrote in message
...
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(I SBLANK
(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(isb lank
(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

Having trouble with formula to automaticly enter data
 

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
-----Original Message-----
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.



--

John

johnf202 at hotmail dot com


"Brian" wrote in
message
...
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(isb lank
(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


.