We help IT Professionals succeed at work.

HowTo: Use table for Data Validation List

ckelsoe
ckelsoe asked
on
Hello,

I am having issues trying to use a table for a data validation list. In my case I have a single column table - created by using the "Format As Table" feature. I have named my table ListStatus.

In the Data Validation dialog box I have selected List and entered ListStatus as the Source. I get an error when doing this.

How can I reference a single column table as the source for data validation?

Thanks in advance.
Comment
Watch Question

Here's a simple explanation:

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

Author

Commented:
That does not seem to work with a range name made by the names created by using the format as table functionality.

Author

Commented:
The names that were created when the table was created are not showing up in the Names section. When I press F5 I can get to the table column but cannot reference the name in a formula.  This is Excel 2007

Author

Commented:
Ok - it looks like I have to create a new name and reference the existing structured table name to get the results I need.

For example, I can create a new name StatusLookup and have it reference =ListStatus[Status]
Finance Analyst
CERTIFIED EXPERT
Commented:
Have you tried just giving the source data a dynamic range name rather than using the format as table function?

For example, use this in Refers to for a range name:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

This will create a one column range starting at A1 and will include as many rows as there are data in column A, assuming continuous data.

This won't appear in the cell address/range address list at top left or in F5 (Edit > Goto) list but can be referred to in a formula and will be highlighted if typed in the Goto window.

Author

Commented:
That would work as well. I just wanted to use the table feature to handle this. I guess the tables implementation is half baked in Excel still.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.