Multiple Functions In One Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

roy.okinawa - 19 Dec 2005 23:20 GMT

This may be a shot in the dark and will be a long formula, but here it goes:

I have multiple columns that I want to condense to one. Therefore, I want
column B to show current status of a work order based on the following:

If column V is blank, column B shows “Open”.

If column V is filled (date) and column J > K, column B shows “Complete/No”.
If column J <= K, column B shows “Complete/Yes”.

Now even though column V is filled, once column AE is filled (date) and
column J > K, column B shows “Closed/No”. If column J <= K, column B shows
“Closed/Yes”.

If column AC and AE are filled (date), column B shows “Closed/BER”.

If column AD and AE are filled (date), column B shows “Canceled”.

Reply to this Message

Pete - 20 Dec 2005 01:04 GMT

Try this out in cell B2 (assumes other data is on row 2):

=IF(V2="","Open",
IF(AND(AD2<>"",AE2<>""),"Cancelled",
IF(AND(AC2<>"",AE2<>""),"Closed/BER",
IF(AE2<>"",
IF(J2>K2,"Closed/No","Closed/Yes"),
IF(J2>K2,"Complete/No","Complete/Yes")))))

This is all one formula - I've just split it at each IF to make it
easier to read. It doesn't test for dates specifically, just whether
the cells are empty or not. Copy down if it meets the criteria.

Pete

Reply to this Message

roy.okinawa - 20 Dec 2005 01:42 GMT

Thanks. That was exactly what I needed.

One more thing I need though, I forgot to add that K may have N/A entered
sometimes. This would result in a "Closed/NA" or "Completed/NA" in B2.

Can you help one more time?

> Try this out in cell B2 (assumes other data is on row 2):
>
[quoted text clipped - 10 lines]
>
> Pete

Reply to this Message

Pete - 20 Dec 2005 10:48 GMT

Try this out in B2 (all one formula, as before):

=IF(V2="","Open",
IF(AND(AD2<>"",AE2<>""),"Cancelled",
IF(AND(AC2<>"",AE2<>""),"Closed/BER",
IF(AE2<>"",
IF(K2="N/A","Closed/NA",
IF(J2>K2,"Closed/No","Closed/Yes")),
IF(K2="N/A","Completed/NA",
IF(J2>K2,"Completed/No","Completed/Yes"))))))

It might be better to insert a new column C (assuming you are using C
at the moment), then you can split this formula so that the words
"Open", "Cancelled", "Closed" and "Completed" appear in column B as
appropriate and the words "BER", "NA", "No" and "Yes" appear in column
C (right aligned).

Pete

Reply to this Message

roy.okinawa - 20 Dec 2005 21:50 GMT

Thanks again and for the recommendation.

> Try this out in B2 (all one formula, as before):
>
[quoted text clipped - 14 lines]
>
> Pete

Reply to this Message