Data Validation: Need to restrict drop-down selection

**splenguin** · 09-04-2009, 02:55 PM

Geniuses, I have one more problem, and then my project will be complete:

Basically, I have two fields that do the same thing [preset selection vs custom entry], and I need to restrict one from holding any data if the other already does. One field is a drop-down menu, and the other is a decimal entry. It was easy to restrict the decimal from being entered if a drop-down selection has already been made - Data Validation prevents any entry unless the drop-down cell is blank. It's not so easy to restrict the drop-down, however, as it already uses List-type Data Validation to allow only values from a specified range.

Does anyone have any ideas for allowing the drop-down to function as normal if a specific other field or range is blank, but not allowing a selection to be made if that specific field or range already has data in it?

I've attached a simplified example worksheet.

Thanks in advance,
joe in MPLS

**ncmay** · 09-04-2009, 03:32 PM

Try out the attached and see is this does what you want.

**Palmetto** · 09-04-2009, 04:03 PM

Use this formula for Data Validation, Custom option for cell B2.

=AND(LEN(A2)=0,ISNUMBER(B2))

Continue to use the list option in cell A2, using AnimalList.

This will prevent an entry in B2 if a selection was made in A2

**splenguin** · 09-04-2009, 04:16 PM

ncmay, I see where you're going; I didn't realize a formula could be used in Data Validation even if List is selected. However, it doesn't appear to be working; I can still select an item from the list even if a decimal has been entered in B2. Curious what the "Num" in your formula means? Couldn't find it in Help..

**ncmay** · 09-04-2009, 06:47 PM

Try changing the formula in the data validation from:
=IF(ISNUMBER(A2,Num,AnimalList)

To:
=IF(ISNUMBER(B2,Num,AnimalList)

Or you can delete the Num named range and reference any vacant cell:
=IF(ISNUMBER(B2,G1,AnimalList)

Num is just another named range that references the empty cell G1.

Hope this helps