Nested If(or(isblank() statement giving me trouble

[jake.peterson]

I have a form built in excel. Based on the value in G20, I want to display the message "This request cannot be processed due to lack of data. See Fields in RED"

G6, G10, G16, G18, and G20 are required on every form. I have that as the first logic test for my formula. After that I want to use the value in G20 to determine if the message should be displayed.

If G20 = "New Hire (ISR)" I want to check the values for D26, D28, D32, D34
If G20 = "New Hire (RIC)" I want to check D26, D28, D32, D38, I26, I28
If G20 = "Role Change with Territory" check D34, D38, I26, I28
If G20 = "Termination" check I20, I40

you get the idea.

In C54 I started the formula:
=IF(OR(ISBLANK(G6),ISBLANK(G10),ISBLANK(G16),ISBLANK(G18),ISBLANK(G20)),"This Form cannot be processed due to lack of data. See Fields in RED",IF(G20="New Hire (ISR)",IF(OR(ISBLANK(D26),ISBLANK(D28),ISBLANK(D32),ISBLANK(D34)),"This Form cannot be processed due to lack of data. See Fields in RED",IF(G20="New Hire with Territory (RIC)",IF(OR(ISBLANK(D26),ISBLANK(D28),ISBLANK(D32),ISBLANK(D38)),"This Form cannot be processed due to lack of data. See Fields in RED","")))))

I got lost in the weeds and I can't figure out where I'm going wrong.

This might be easier to do with a case statement in a macro but I'm not sure how to initiate the macro when they select a value for G20.

any help/advice is appreciated. Thank you

 

[Scott Huish]

What kind of Data are in those cells? Numeric or Text?

 

[jake.peterson]

Dates, and text

 

[Scott Huish]

How about this?

=IF(OR(COUNTA(G6,G10,G16,G18,G20)<5,AND(G20="New Hire (ISR)",COUNTA(D26,D28,D32,D34)<4),AND(G20="New Hire (RIC)",COUNTA(D26,D28,D32,I26,I28)<5),AND(G20="Role Change with Territory",COUNTA(D34,D38,I26,I28)<4),AND(G20="Termination",COUNTA(I20,I40)<2)),"This request cannot be processed due to lack of data. See Fields in RED","")

 

[jake.peterson]

Wow. this works exactly like I want it to.

Thanks Hotpepper!