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

Will the instructions here help you?

http://www.excel-easy.com/examples/dependent-drop-down-lists.html

Answer 2

Thanks for the help, Jason. I don't think that method will work for my particular situation. Maybe a bit more explanation is required.

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.

Thanks again for taking a peek at this, and let me know if you have any ideas.

Answer 3

Try suffixing =indirect(your formula)

XL in Excel

Answer 4

I'm not sure I did it correctly, since it returned #ref errors.

Answer 5

Can you upload snowsa data in form of Excel and share the link??