Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel Validation Rules

  Asked By: Jordon    Date: Sep 18    Category: MS Office    Views: 843
  

I created a drop down list with 15 entries in it (ex. Animal types).
I then copied and pasted that same drop down list into 14 rows under
it. If I chose one entry in my first dropdown list (say "dogs"), I
want the next list directly under that to reflect that choice so the
following list will not contain "dogs" it will just contain the other
14 entries and so on down the list so the last row (row 15) would only
have 1 entry to choose from. Is there a way I can do this with custom
validation formatting? I would prefer not to use VBA code if it can
be avoided. Can someone please help?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Daw Boonliang     Answered 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.

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




Tagged: