Hi,
In Excel 2007... Is there any way I can use a column in a Named Table as
my data validation list without specifying the range. For example I have a
table named MyTest of ID/Name. I want to display the Name column as my data
validation list. Any time I try using my source as =MyTest[Id] in the
DataValidation List Source I get an error. I want to find a way to do this
without specifying the given range within my table because my tables might
use different start range cells.

Thanks,
Keith

Hi Keith,

Yes, you can definitely use a column in a named table as your data validation list without specifying the range. Here's how you can do it:

1. Select the cell where you want to apply the data validation.
2. Go to the Data tab and click on Data Validation.
3. In the Data Validation dialog box, select List from the Allow drop-down menu.
4. In the Source field, enter the following formula:
   Code:

   =MyTest[Name]

5. Click OK.

This will create a data validation list based on the values in the Name column of your MyTest table. The formula refers to the entire column of Name in the MyTest table, so you don't need to specify a range.

Hi Keith

In Name Manager create a new name called myList with a reference of
=MyTest[Id]
In DV, use List, Source myList
--
Regards
Roger Govier

"keith" wrote in message
...
Hi,
In Excel 2007... Is there any way I can use a column in a Named Table as
my data validation list without specifying the range. For example I have a
table named MyTest of ID/Name. I want to display the Name column as my
data
validation list. Any time I try using my source as =MyTest[Id] in the
DataValidation List Source I get an error. I want to find a way to do this
without specifying the given range within my table because my tables might
use different start range cells.

Thanks,
Keith

That works great. Thanks.

Keith

"Roger Govier" wrote:

Hi Keith

In Name Manager create a new name called myList with a reference of
=MyTest[Id]
In DV, use List, Source myList
--
Regards
Roger Govier

"keith" wrote in message
...
Hi,
In Excel 2007... Is there any way I can use a column in a Named Table as
my data validation list without specifying the range. For example I have a
table named MyTest of ID/Name. I want to display the Name column as my
data
validation list. Any time I try using my source as =MyTest[Id] in the
DataValidation List Source I get an error. I want to find a way to do this
without specifying the given range within my table because my tables might
use different start range cells.

Thanks,
Keith

I have a similar question. Is it possible to use a Named Table column in Data Validation? (A Named Table, not a Named Range).

Attached might help you with your query.

There are 2 samples of calling a named table via a Data Validation.

First one uses the OFFSET function. The named range is the first cell of the table which is used as a starting reference for the OFFSET function.

The second one uses an array to support the TRANSPOSE function. The named range is the table itself.

I could not find the Named Table in this file. I found four Named Ranges--Table1, Table2, Tbl_1, and Tbl_2. Where is the Named Table?

It's Tbl_1 and Tbl_2

I meant to say that I am using Excel 2007. Are you? In Excel 2007, when you left- click any cell in a Named Table, you get a menu "Table Tools" and submenu "Design". When you right click, you get options like "Insert"/"Table Columns to the Left". None of these things happen with any of the cells in your example. In fact, I think they are Named Ranges, not Named Tables.

Excel 2007 Named Tables fix some serious limitations of Named Ranges, and I am trying to switch from using Named Ranges to using Named Tables as much as possible. Going back to using Named Ranges as you seem to be suggesting defeats my present purpose.

Oh that kind - TABLE LIST and not an ordinary table. My bad.