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

#### jake.peterson

##### Board Regular
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
What kind of Data are in those cells? Numeric or Text?

Last edited:
Dates, and text

=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","")

Wow. this works exactly like I want it to.

Thanks Hotpepper!

Replies
9
Views
519
Replies
0
Views
807
Replies
15
Views
362
Replies
5
Views
410
Replies
0
Views
376

1,196,480
Messages
6,015,448
Members
441,895
Latest member
Zululander

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back