Excel 2016 - unable to use named table for List data validation

Question

I have created a workbook that contains a named table that is one column wide and contains text values. The table name appears in the Name Manager with Workbook scope and references the range of cells that I expect it to reference. When I attempt to use the table name as the source for List data validation (to create a drop-down list), I get the following error:

I can successfully use named ranges (even dynamic named ranges), but I want to simplify my workbook by using named tables while retaining the benefit of automatic expansion and contraction of my validation list. 

Any help is greatly appreciated!

Answer 1

You can reference a table directly for the list. You need to use INDIRECT and the table and column name. Like this:

=INDIRECT("MyList[MyList]")

If the column name is the same as the table name.

Here’s more help:

https://blackatlascreative.com/blog/create-an-e...

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Answer 2

Not can. Can’t.

Answer 3

You can't reference a table directly for the list. You need to use INDIRECT and the table and column name. Like this:

=INDIRECT("MyList[MyList]")

If the column name is the same as the table name.

Here’s more help:

https://blackatlascreative.com/blog/create-an-e...

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Thanks, Jason. Sad state of affairs, but I guess I've gotta do what I've gotta do :) (I corrected your typeo in the quote above)

One note, your column names cannot have spaces in them (e.g. "My List"). I tried escaping quotes ( =INDIRECT("MyList[""My List""]") and that didn't work.