Lookup list dependant on the contents of another cell

Question

hope someone can help to crack this one?

I've got a table on my data entry tab (sheet 1) where each row includes two data validation drop down lists (source is on sheet2). I want the options in the second drop down list (k13) to be dependant on what is selected in the first drop down list (g13)...

ie. if you select 'Diary' in the first drop down box then the second drop down only has 'Cheese', 'Milk' and 'Yogurt' - likewise, if you select 'Fruit' in the first box - I want the only options to be available specific to that categories column in the lookup table on sheet 2 (which are 'apple', 'pear' and 'banana' in this case)?

Please help - i think this is a vlookup indirect formula??

Answer 1

I would make this a comment, but then the information may be lost. You're looking for dependent validation. this page has some good information. And I quote:

---

Open a new Workbook and on Sheet1 enter these heading into cells A1:D1 Expensive Cars, Cities, Names, Countries

Now place some relevant entries below these headings, down to say row 5. Now select A1:D1, click in the Name Box (left of the formula bar) type the name: List1 and push Enter. Now select A1:D5 (or the last row of the longest list) and go to Insert>Name>Create. Ensure only "Top Row" is checked and click Ok. If you now go back to the Name Box you will see 5 Named ranges. Note how Excel has used the Underscore for "Expensive_Cars". This is because Named ranges cannot have spaces.

Now click onto Sheet2 and select cell A1. Go to Data>Validation, choose the "List" option then type: =List1 in the Source box. Ensure "In-cell dropdown" is checked and click Ok. Now select cell A2 and again go to Data>Validation, choose the "List" option then type: =INDIRECT(SUBSTITUTE($A$1," ","_")) in the Source box. Ensure "In-cell dropdown" is checked and click Ok.

Now select a list from cell A1 and you will get the appropriate list in cell A2.

Notes

• Normally, a Validation list cannot refer to a list from another Worksheet. This can be overcome by naming the list and using its Name over its address.
• The INDIRECT Function return the reference returned by a text string. This means, rather than the Validation List in A2 seeing the content of A1 as a text string, it sees it as a range name .
• The SUBSTITUTE Function is used to replace any spaces in the text of A1 with a Underscore. This is vital when we have items in List1 like: Expensive Cars.