Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Jordon Willis   on Sep 18 In MS Office Category.

  
Question Answered By: Daw Boonliang   on Sep 18

I can do this but the validation  list will contain blank lines as well
as valid entries, but it'll work after a fashion.. it was much more
difficult not using vba. You are not confined to starting from the top
down.

I've done this with 4 instead of 15 entries  but you'll get the drift:
I arbitrarily chose cells C4:C7 on sheet1 to be where the dropdowns
will be; Name this range 'soFar'. For cell C4's validation settings
choose list  in the Allow: box, and type in:
=valList
in the Source: field

copy this cell to cells C5:C7

Now to sheet2, a blank sheet (which can be hidden later), in cells
A1:A4 enter your valid criteria (your animal types  list).

In cell B1 enter this formula:
=IF(ISNA(MATCH(A1,soFar,0)>0),A1,"")

and fill down to B4.
Name this range (B1:B4):
ValList
(This is the range which will contain the unchosen options only and is
the list used in the validation criteria)

Go back to sheet1 and try it .. it worked fine here. Hide sheet2 if
you want.

Share: 

 
 
Didn't find what you were looking for? Find more on Excel Validation Rules Or get search suggestion and latest updates.


Tagged: