Multiple Functions In One Column

R

roy.okinawa

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â€.
 
P

Pete

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
 
R

roy.okinawa

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?
 
P

Pete

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
 
Top