range is blank

0

How can i create a formula in remarks showing the results not yet started, in process, rtd and delivered. if  range from lam_on_date to delv_date is blank/has no date value the remarks should be "NOT YET STARTED". If range lam_on_date to asmbly_ondate has a "datevalue" and asmbly_offdate is blank the remarks should be "IN PROCESS". If range lam_ondate to asmbly_offdate has a date value and delv_date is blank the remarks should be "RTD". if delv_date has a date value the remarks should be "DELIVERED" 

LAM ON DATE LAM OFF DATE CUT ON DATE CUT OFF DATE BAND ON DATE BAND OFFDATE ASMBLY ONDATE ASMBLY OFFDATE DELV DATE REMARKS                   NOT YET STARTED     10/24/17 10/25/17 10/26/17 10/26/17 10/27/17 10/29/17   NOT YET STARTED     10/24/17 10/25/17 10/26/17 10/26/17 10/27/17 10/29/17   IN PROCESS     10/19/17 10/19/17 10/19/17 10/20/17 10/23/17 10/25/17 11/3/17 IN PROCESS     10/19/17 10/19/17 10/19/17 10/20/17 10/23/17 10/25/17 11/3/17 READY TO DELIVER     10/24/17 10/25/17 10/26/17 10/27/17 10/27/17 10/29/17   READY TO DELIVER     10/24/17 10/25/17 10/26/17 10/26/17 10/27/17 10/29/17   IN PROCESS     10/25/17 10/26/17 10/26/17 10/27/17 10/27/17 10/29/17   IN PROCESS
Answer
Discuss

Answers

0

If you wish to check whether the entries are valid dates I currently have no idea of how to do that without VBA. However, you may like to try the formula below which merely checks for cell content. Enter it in the Remarks column in row 2 and copy down as required.

=INDEX({"NOT STARTED","IN PROGRESS","RTD.","DELIVERED"},MATCH(IF(COUNTA($A2:$F2)=6,COUNTA($A2:$I2),0),{0,7,8,9},1))

To understand this formula you must know that curly braces within a formula represent arrays. {"NOT STARTED","IN PROGRESS","RTD.","DELIVERED"} could be replaced with something like $Z1:$Z4, provided that Z1:Z4 holds the four texts you need. The same goes for {0,7,8,9}.

So, the formula basically says, "n is the position of X in {0,7,8,9} (meaning 1, 2, 3 or 4) and the result is the nth element of the array {"NOT STARTED","IN PROGRESS","RTD.","DELIVERED"} "

X = IF(COUNTA($A2:$F2)=6,COUNTA($A2:$I2),0), meaning, if all 9 cells A:I have a value x = 9, if all cells A:H have a value x = 8, and if all cells A:G have a value x = 7. This calculation would be prone to errors if cells G:I are filled but one of the cells A:F is left blank. Hence the test IF(COUNTA($A2:$F2)=6. If one of these cells is blank the IF formula evaluates to zero which leads to "NOT YET STARTED".

Therefore I have modified your plan to include one extra message, "MISSING DATA" which would be displayed if one of the cells which should have dates in them is left blank.

=INDEX({"MISSING DATA","NOT STARTED","IN PROGRESS","RTD.","DELIVERED"},MATCH(IF(COUNTA($A2:$F2)=6,COUNTA($A2:$I2),1),{0,6,7,8,9},1))
Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login