Data Validation error - dependent drop down list

Question

I am attempting to create a dependent drop down list that uses a formula to extract data from a table in a separate document. The list I'm attempting to create is based on an initial drop down list with source data (company names) that have been hard-entered into the source box of the data validation tool. The second list attempts to return a list of contacts for the company selected in the first drop down list.

The formula I'm using is this:

=OFFSET(Customer_Database_Test.xlsx!Table1[[#Headers],[Contact]],MATCH($C$1,Customer_Database_Test.xlsx!Nickname,0),0,COUNTIF(Customer_Database_Test.xlsx!Nickname,$C$1))

When I press F9 inside this formula, it returns data exactly as expected, providing this response for "ABC" (in this example, "ABC" is the customer selected in the first drop down list):

={"Jane D";"John D";"Bill D";"Paula D"}

The problem occurs when I past the formula into the source box of the data validation tool. I receive this message: "There's a problem with this formula...."

Here's more detail:

I'm using two separate Excel documents. The first is a table that serves as a customer database and lists multiple customer contacts.

The second document is attempting to create a drop down list of contacts depending on the customer selected in the first drop down list.

For the second drop down list, I'm attempting to use the formula that I confirmed is returning the proper data as described in my initial post.

Clicking OK triggers this error message:

I'm not sure what other method I could use to return the values that I need. Ultimately, my goal is to use this database to feed other worksheets, too, in a similar way.

Any ideas on how I can fix this problem?

Answer 1

Let me know if this works for you:

https://www.dropbox.com/s/ivljl7sf0sscfrc/Customer\_Database\_Test.xlsx?dl=0

Answer 2

I had the same issue, with the same error.

Turns out that the Data Validation window doesn't like it when you use named tables - I converted all my "Format as Table" tables back to ranges (using Convert to Range), and the error disappeared. Who knew!

I only format them as tables as I like the pretty colours... ;-)

HTH.

Answer 3

Thanks, Daniel, for the thought. When I make that switch, I now get this error message: "This type of reference cannot be used in a Data Validation formula." I don't believe that's true, but I can't seem to make it work. Crazy.

Answer 4

Hi Steve,

You can try changing the name species to a global name in your sheet.

Here is a post for your reference: Problems with Excel 2010 Data Validation.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Regards,

Tisky

Answer 5

Hi Steve,

Have you tested the steps in the post above?

Regards,

Tisky