IF Formula to Specify if cell is empty for users

[kumara_faith]

Hi,

I have the following table:

In the message column, I am trying to have a message for users if:
1) If all cells from Branch to No of Pages is blank, then no message
2) If any of the cells from Branch to No of Pages is blank, then to display message for example " Date is blank. Please complete". This is assuming the Date cell is blank and all other cells is filled.
3) If some of the cells are filled and others are blank, to display message to specify the cells that is blank. Example, Branch, Date and Transaction Type is filled but no of Policy and No of Pages are blank. So message should be " No of Policy and No of Pages are blank. Please Complete".
3) if all cells from Branch to No of Pages is filled, then display message "Complete"

I tried with the following formula but it not working as intended:

=IF(COUNTA(B5:F5)="","",IF(B5="","Branch is blank. Please complete",IF(C5="","Date is blank. Please complete",IF(D5="","Transaction Type is blank. Please complete",IF(E5="","No of Policy is blank. Please complete",IF(F5="","No of Pages is blank. Please complete","Complete"))))))

Appreciate any help.

 

[kumara_faith]

Hi FormR,

Thank you.

If all cells are empty, the formula returns as "Please complete" instead of blank. How do I modify this ? Thanks.

 

[FormR]

Hi, oops - I didn't test that very well!

Try this instead:

=IF(COUNTA(A2:E2)=0,"",IF(COUNTBLANK(A2:E2)=0,"Complete",TEXTJOIN(" and ",TRUE,FILTER(A$1:E$1&" is blank.",A2:E2="","")&" Please Complete")))