CODEWITHSUNDEEP
excelexcel-formula
vasa.dhananjay
vasa.dhananjay

Reputation: 165

Excel: Using table as source for dropdown

I'm using a table I've named Table_Example with a single column with the header Data so range of values is A2:A.

In another sheet, I want to create a drop down with the nonempty values from this table. I'm creating this drop down through the data validation option.

The problem is that when I type in =Table_Example[Data] as the source, I get the following error:

We found a problem with this formula...

Any idea what I'm doing wrong?

I think I read somewhere that a drop down created from a table can have empty rows which won't show up in the drop down.

Upvotes: 2

Views: 1511

Answers (3)

Smarties
Smarties

Reputation: 23

This got it working for me:

  1. Create a new Name and give it 0 or select some random cells on your sheet.

  2. Select the dropdown Cells and set as Value this new empty name you just created. =DD_new_name_for_data

  3. Now you can give this Name the value from your table you wanted for the dropdown. DD_new_name_for_data = Table_Example[Data]

I dont know why but when you set the value of the "new name" to the table and then try to set this "new name" as value for the dropdown it gives an error. So you need to do it in the order explained above.

Upvotes: 1

Al Cher
Al Cher

Reputation: 408

You cannot put a Table name in Source for Data Validation. This parameter should contain a list. For your purpose you have to use indirect reference, so your Source should contain formula: =INDIRECT("Table_Example[Data]") Cheers

Upvotes: 2

nbayly
nbayly

Reputation: 2167

In the Data Validation wizard there is a specific checkbox to ignore blanks. So you could set the data to be A:A and it will ignore blank spaces. I think your error relates to how your referencing your table. I'm not sure what you mean with [Data]. You should just place the defined name of the table (ie =Table_Example) or the range in formula format.

Hope this helps. Cheers,

Upvotes: 0

Related Questions