Creating a Dependent Validation List

**cpalmer72** · 01-31-2013, 11:14 AM

I am trying to create a dependent validation that will allow me to have a list of account numbers and a list of associated names. I want to be able to pull up the name of the account (from sheet "Accounts" column c) in one cell and have the account number (from sheet "Accounts" column B) come up automatically (or as a dependent drop down menu item) in the second cell. What is the best way to set this up?

In the attached example, I want fields in row 9 columns C-K to have a list of options from column C on the "accounts" worksheet and the fields in row 10 columns C-K to pull up the account number from column B on the "accounts" worksheet. If I put "Machinery & Equipment" into field 9C, I want to have 10C read "354451".

**Melvinrobb** · 01-31-2013, 11:37 AM

The following formula in C10 should work:

Formula:

Please Login or Register  to view this content.

**cpalmer72** · 01-31-2013, 11:56 AM

I can't seem to get it to work. It keeps returning "$-".

**Melvinrobb** · 01-31-2013, 11:57 AM

I am not sure what the story is with your file. When you type in a formula, it literally replaces the formula with a different value. If you remove the formatting, or validations, it should work.
I tried the formula on a different sheet without the formatting and it worked.

**cpalmer72** · 01-31-2013, 12:11 PM

Ok. I converted the tables to a range and now it works perfectly! Thanks so much!

**Melvinrobb** · 01-31-2013, 12:38 PM

No Problem. I'm glad you posted a sample workbook. It would be very difficult to figure out that formatting issue without it.
Remember to change the post to "solved" (under "Thread Tools" in the top right corner").