Dependent Data Validation Lists

**PHUAG** · 01-30-2010, 09:13 PM

Hi,
In Excel 2007, are you able to create a dependent list via Data Validation?
In Column A, I've created a list where the user selects a particular department (e.g. HR, Finance, IT).
In Column B, I would like to create a list which will allow the user to select names for that particular department only (i.e. if they had selected Finance at Column A, then I only want the list at Column B to show employees working in the Finance team).

I've tried searching this forum but am constantly receiving "fatal error" messages. Apologies if there is an answer already posted here.

Many thanks!

**jeffreybrown** · 01-30-2010, 09:27 PM

Here is a great place to start...

This site will walk you through step by step...

http://www.contextures.com/xlDataVal02.html

**teylyn** · 01-30-2010, 10:47 PM

Phuag, the forum search is less than satisfactory. If you want good results, try a Google site search like

"my search expression" site:excelforum.com

**PHUAG** · 01-30-2010, 11:11 PM

Thanks rocky1. The attached spreadsheet was very useful for my initial enquiry. I've never written in VB before and am now trying to clear the employee name when a new dept is selected. I am able to achieve this for one row by using:

Please Login or Register  to view this content.

What is the syntax if I want to clear Columns E & F when any changes are made to D9 to D100?
e.g. If D56 is changed, then E56 * F56 are to be cleared?

I am hoping there's an easier way than writing multiple if statements!

**jeffreybrown** · 01-30-2010, 11:39 PM

Hi Phuag,

I believe the first part would be changed to --

Please Login or Register  to view this content.

but I do not know what to do on the second --

I tried (which I didn't believe would work) and that is confirmed, no good --

Please Login or Register  to view this content.

Maybe somebody else will bail me out here...

**NBVC** · 01-30-2010, 11:41 PM

PHUAG and Rocky1,

Your posts do not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**PHUAG** · 01-31-2010, 01:37 AM

Hi again,

I am unable to create a dependent list which is dynamic in nature. For some reason, only the first value in the list is being returned.

Please refer to the attachment "Test" where in the "Staff to Complete Dynamic" tab at C14:C21, I want the data validation list to refer to the list of employee names which relate to that particular department. The list of names are in the Dynamic Lists tab. At C14, I tried using the data validation formula as per your instructions below but it doesn't work. Does it have anything to do with the fact that your file is Excel 03 & mine is 07 macro-enabled?

Using Dynamic Lists

Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with dynamic lists. Instead, you can use the following method:

1. Create the first named range and dropdown list as described above.
2. Create the supporting named lists, and name the first cell in each range, e.g. cell B1 is named Fruit and cell C1 is named Vegetables.
3. Name the column in which each list is located, e.g. column B is named FruitCol and column C is named VegetablesCol
4. For the second dropdown, choose to Allow: List, and use a formula that calculates the lookup range. For example, if the first dropdown list is in cell E2:

=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col")),1)Thanks.