Data Validation with nested IF statements

[joferder]

I was making good progress with this, but seem to have run into a roadblock now and can really use some help. I am trying to make it so that I have a drop-down menu that will have data based off of what is in cells B6 and C6. Cell B6 will have two options (in a drop-down); "Minot AFB" and "91 MW". Cell C6 will have three options (also in a drop-down); "Advisories, "Watches", or "Warnings". The column that I am trying to get the nested IF statements in the data validation is in column "E". The lists are created on tab "Sheet3" in columns E and F.

Idealy, the drop-down in colum "E" would show a result of "Select Location and Type" if either B6 or C6 is blank. After that I would like to have the drop-downs give only the possible choices based off of the 6 different combinations of B6 and C6 (i.e. - Minot AFB and ADVISORY, Minot AFB and WATCH, Minot AFB and WARNING, 91 MW and ADVISORY, etc.). Here is the formula that I came up with:

=IF(OR(ISBLANK($B$6)=TRUE,ISBLANK($C$6)=TRUE),"Select Location and Type",IF(AND($B$6="Minot AFB", $C$6="ADVISORY"),Sheet3!$E$3:$E$14,IF(AND($B$6="Minot AFB", $C$6="WATCH"),Sheet3!$E$17:$E$24,IF(AND($B$6="Minot AFB", $C$6="WARNING"),Sheet3!$E$27:$E$36,IF(AND($B$6="91 MW", $C$6="ADVISORY"),Sheet3!$F$3:$F$6,IF(AND($B$6="Minot AFB", $C$6="WATCH"),Sheet3!$F$17:$f$23,IF(AND($B$6="Minot AFB", $C$6="WARNING"),Sheet3!$F$27:$F$35,"ERROR")))))))

I created the formula in notepad and then tried to paste it into the Data Validation form, but won't allow this. I am assuming that it is because the formula is too long, but I don't know how to make it shorter and still get the functionality that I need/want. My guess is that there is probably a few errors in the formula anyway, but I can't test it since it won't let me past it in.

Any help or advice that anyone can give me would be great. I'm not really sure where to go from here.

 

[T. Valko]

I was making good progress with this, but seem to have run into a roadblock now and can really use some help. I am trying to make it so that I have a drop-down menu that will have data based off of what is in cells B6 and C6. Cell B6 will have two options (in a drop-down); "Minot AFB" and "91 MW". Cell C6 will have three options (also in a drop-down); "Advisories, "Watches", or "Warnings". The column that I am trying to get the nested IF statements in the data validation is in column "E". The lists are created on tab "Sheet3" in columns E and F.

Idealy, the drop-down in colum "E" would show a result of "Select Location and Type" if either B6 or C6 is blank. After that I would like to have the drop-downs give only the possible choices based off of the 6 different combinations of B6 and C6 (i.e. - Minot AFB and ADVISORY, Minot AFB and WATCH, Minot AFB and WARNING, 91 MW and ADVISORY, etc.). Here is the formula that I came up with:




I created the formula in notepad and then tried to paste it into the Data Validation form, but won't allow this. I am assuming that it is because the formula is too long, but I don't know how to make it shorter and still get the functionality that I need/want. My guess is that there is probably a few errors in the formula anyway, but I can't test it since it won't let me past it in.

Any help or advice that anyone can give me would be great. I'm not really sure where to go from here.

When I was in the AF stationed at Kincheloe MI (back in the 70's) we had a saying that went something like this: Why not Minot?

A formula for data validation must be less than 256 characters long. Your formula is 438 characters long.

See if this helps:

http://contextures.com/xlDataVal02.html

 

[joferder]

A lot of people still say that...not just AF people. And the answer is always: "Freezins the reason."

Thanks for the link. I had seen that page during one of my many google searches, but I was having a hard time figuring out how to use it, but I worked on it more and I got it to work and it works perfectly.

Thanks again for the help.

 

[T. Valko]

A lot of people still say that...not just AF people. And the answer is always: "Freezins the reason."

Yep, that's it!

Thanks for the link. I had seen that page during one of my many google searches, but I was having a hard time figuring out how to use it, but I worked on it more and I got it to work and it works perfectly.

Thanks again for the help.

Good deal. Thanks for the feedback!

 

[joferder]

I actually do have one other questions pertaining to this. Is there a way to fill down the data verification where it change the cell that it pulls the data from? The current formula that I have in the data validation is:

=OFFSET(LocationStart,MATCH($T$10,LocationColumn,0)-1,1,COUNTIF(LocationColumn,$T$10),1)

The problem is that when I get to row 11 I need it to look at T11, but fill it down will always make it so that it looks at T10. I am really hoping that there is an easy way to do this rather than having to put it in ever cell since I would have to do this for about 60 different cells and it will take a lot longer to do that way.

Thanks again for your awesome help.

 

[T. Valko]

I actually do have one other questions pertaining to this. Is there a way to fill down the data verification where it change the cell that it pulls the data from? The current formula that I have in the data validation is:



The problem is that when I get to row 11 I need it to look at T11, but fill it down will always make it so that it looks at T10. I am really hoping that there is an easy way to do this rather than having to put it in ever cell since I would have to do this for about 60 different cells and it will take a lot longer to do that way.

Thanks again for your awesome help.

If I understand...

You need to remove the dollar sign from the row reference:

=OFFSET(LocationStart,MATCH($T10,LocationColumn,0)-1,1,COUNTIF(LocationColumn,$T10),1)

 

[joferder]

Well now I just feel like an idiot. Right after I read your reply, it made perfect sense.

Thanks again for all your help. You have saved me from several days of work that should have been simple.

 

[T. Valko]

Well now I just feel like an idiot. Right after I read your reply, it made perfect sense.

Thanks again for all your help. You have saved me from several days of work that should have been simple.

You're welcome. Thanks for the feedback!