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.

 

[FormR]

Hi, here's an option you can try:

Book1
	A	B	C	D	E	F
1	Branch	Date	Transaction Type	No of Policy	No of Pages	Message
2
3	a	a	a	a	a
4	a	a	a	a		No of Pages is blank. Please Complete
5	a			a		Date is blank. Please Complete and Transaction Type is blank. Please Complete and No of Pages is blank. Please Complete
6		a	a		a	Branch is blank. Please Complete and No of Policy is blank. Please Complete
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IF(COUNTA(A2:E2)=0,"",TEXTJOIN(" and ",TRUE,FILTER(A$1:E$1&" is blank. Please Complete",A2:E2="","")))

 

[Jimmypop]

Hi, here's an option you can try:

Book1
	A	B	C	D	E	F
1	Branch	Date	Transaction Type	No of Policy	No of Pages	Message
2
3	a	a	a	a	a
4	a	a	a	a		No of Pages is blank. Please Complete
5	a			a		Date is blank. Please Complete and Transaction Type is blank. Please Complete and No of Pages is blank. Please Complete
6		a	a		a	Branch is blank. Please Complete and No of Policy is blank. Please Complete
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IF(COUNTA(A2:E2)=0,"",TEXTJOIN(" and ",TRUE,FILTER(A$1:E$1&" is blank. Please Complete",A2:E2="","")))

With the permission of FormR and his formula above that was used... If you also want it to say "Complete" then can try below... FormR can just inform if it will be correct like this but have tested and working fine on my side if all cells are filled...

Book1
	A	B	C	D	E	F
1	Branch	Date	Transaction Type	No of Policy	No of Pages	Message
2	a	a	a	a	a	Complete.
3
4	a	a	a	a	a	Complete.
5	a			a		Date is blank. Please Complete.Transaction Type is blank. Please Complete.No of Pages is blank. Please Complete
6		a	a		a	Branch is blank. Please Complete.No of Policy is blank. Please Complete
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=IF(COUNTBLANK(A2:E2)=0,"Complete.",IF(COUNTA(A2:E2)=0,"",TEXTJOIN(".",TRUE,FILTER(A$1:E$1&" is blank. Please Complete",A2:E2="",""))))

 

[FormR]

If you also want it to say "Complete" then can try below

Hi, I missed that part of the requirement - thanks for stepping in

 

[Jimmypop]

Hi, I missed that part of the requirement - thanks for stepping in

Lol... No hassles... We all need to help where we can

 

[kumara_faith]

Hi FromR and Jimmy,

Thank you and the solution worked. Appreciate your patience and have a great day.

 

[FormR]

Hi, great!

Thinking about it though, the "complete" element can be dealt with more simply like this.

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

 

[Jimmypop]

Hi, I missed that part of the requirement - thanks for stepping in

Lol... No hassles... We all need to help where we can

 

[kumara_faith]

Hi,

Just to check. Instead of the message repeating the phrase "Please complete" for all cells that is incomplete, is there a way to modify to say "Please complete" only once at the end of the sentence? Example is as follows:

"Date is blank. Transaction is blank. No of policy is blank. Please complete."

 

[FormR]

is there a way to modify to say "Please complete" only once at the end of the sentence?

Hi, you could try like this:

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