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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
range is blank
0
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))