How to incorporate validation list in custom validation?

[ohlalayeah]

I have an excel file, I want to prompt user that the cell B7 is not available while value of cell B4 is "N/A", cell B7 is a drop down list.

I want to add =ISERROR(SEARCH("N/A",$B$7)) to custom validation in cell B7 but meanwhile I need B7 as a drop down list. How can I make that?

is there any formula like =if(ISERROR(SEARCH("N/A",$B$7)), prompt error, else let user choose from drop down ?

Thanks

[benishiryo]

hi there. you can try going to B7 & change your Data Validation formula to:
=IF($B$4="N/A","",transport)

and in another tab in Data Validation is "Input Message". in there you can type a message that shows when they click on B7. so you can type in the Input Message something like:
If you select "N/A" in B4, you will not be able to select anything here

[ohlalayeah]

Thanks! but B7 no longer becomes a drop down list. I want it to be a drop down list but also have that "if statement".

[benishiryo]

hmmm what do you mean? B7 is still a dropdown, but you cannot select anything if B4 is "N/A". if you move to B7, you should still see that dropdown icon.

so what do you want to see in B7 if B4 is "N/A"? "N/A" too?

your IF statement is incomplete. it is saying if it cannot search for "N/A" in B7.... then what?

[ohlalayeah]

I will be prompted a warning and not able to choose anything in B7 if B4 is "N/A".

[ohlalayeah]

anyone can help plz? tks!

[Fotis1991]

Hi

See if this can helps you.

[ohlalayeah]

Hi Fortis,

Thanks. I want to allow user manual enter value in B7, but when B4 is "N/A" I want a prompt when entering any value in B7 and not able to select from drop down. but when B4 is not "N/A", user is allowed to choose from drop down in B7 and is able to enter any value in B7.

[Fotis1991]

Fotis..Not Fortis..

1) Manual enter in a validation lisr, cell??
2)In my suggestion, YES, user is allowed to choose ONLY an option that inform him(her) that can not choose anything..as B4 is N/A.

See this option. Not allowed to choose anything..

[ohlalayeah]

Thanks again Fotis!

When B4 is "N/A", user is not allowed to choose anything in B7 and not allowed to enter anything manually in B7.
When B4 is not "N/A", user is allow to choose from drop down and manually enter any value in B7.

[Fotis1991]

Sorry. This was my best....I am quit.

[ohlalayeah]

i have corrected my statements in #11