• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Having multiple validated lists in excel

F

findshals

New Member
Hi , I have been using this website for my work a few times. Been much more helpful than any other excel site I have used. I needed a help with multiple data validation.

In order to avoid complication I am simulating an e.g. which if solved would help solve my problem. I have two lists Food and type of food.

Under good column I can have items such as fruits , vegetables and grains. This is one of the lists that need to be validated. Now once the person entering data chooses say fruits, then in the adjacent column he should be able to enter data only from the pre- defined list of fruits. SImilarily if in column food he chooses vegetables then he should have to chooses from the pre defined list of vegetables. Can this be done easily?
 
Findshals


Firstly, Welcome to the Chandoo.org forums


What you want to do is called Dependent Data Validation


I refer everyone to the Contextures web site which has a comprehensive discussion on the topic: http://www.contextures.com/xlDataVal02.html
 
Hi . Thanks . I did check out the contextures website. Had been stuck with this fr more than 2 weeks now. Actually even my e.g. are as what was given on contextures.Somwhow only one list worked so I wanted to know if there was an alternate way.
 
The techniques at Contextures work so you must have done something slightly wrong

can you post the file for us to review

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi. One of the problems of the multiple lists not featuring has been resolved. But I still have an issue.If I add any more new entries to any of the lists they do not get covered and I am not able to add to them. I am not able to extend the name range to them.

I am now able to get both the lists


http://www.fileconvoy.com/dfl.php?id=g358025e672380ee99992600787dc38746500a503e
 
Hi ,


One way to get a dynamic named range is by defining it as follows :


=Sheet2!$A$3:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A)+1)


This is how your named range Category has been redefined ; you can follow this same technique to redefine all the other named ranges , which at present are defined in terms of absolute cell addresses.


The +1 is because row 1 is blank , and hence the last row of data needs to be extended by that 1 row.


Narayan
 
You must log in or register to reply here.
Back
Top