I would only like to be able to fill cell B2 with values "x", "y", OR "z" only if A2 is filled with any value.
I have come up with a solution, but I don't like it. It's very long. Currently I have this Data Validation in B2: =OR(IF($A2<>"",$B2="x"),IF($A2<>"",$B2="y")).
The problem is that I have 32 unique "xyz's". Could I do anything with a named range? My attempts thus far, have not proved fruitful.
With your 32 acceptable values in H1:H32
Custom and uncheck "Ignore Blank"
Formula
=AND(LEN(A2)>0,ISNUMBER(MATCH(B2,$H$1:$H$32,0)))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
This should also work, and may be easier to customize. Create a Data Validation List, and use this as your source:
=IF(A2="","",$H$1:$H$32)
=AND(LEN(A2)>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"x",""),"y",""),"z",""))=0)
try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks