Data validation list from table

[Jon]

Is it possible to create a data validation list using a table for the list?
I can change sheets and select a range in the colum, but if the data in the
table increased then the formula would have to be adjusted every time.

 

[Per Jessen]

Hi

The short answer is Yes.

If you want to have your validation list in another sheet, you have to name
the range and then in Source field enter the name refering to the validation
table.

Include a number of empty fields in your named range and check Ignore blank
in the Data Validation window.

Best regards,
Per

 

[Dave]

Hi Jon,
http://www.contextures.com/xlNames01.html#Dynamic
This link describes how to create Dynamic Named Ranges, which means that you
can add data to your list, and have the Named Range expand to include the new
data.
You can then use that Named Range in your validation.
Note: Your list data must be contiguous ie no blanks, for the dynamic thingy
to work properly.
Regards - Dave.

 

[Gord Dibben]

Per

You misunderstand the purpose of "ignore blanks"

Blank Cells in Source List

If the source list is a named range that contains blank cells, users may be able
to type any entry, without receiving an error message. To prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Jon

Maybe check out Debra's site for creating dynamic named ranges so's blank cells
are not included.

http://www.contextures.on.ca/xlNames01.html#Dynamic

And this page may help.

http://www.contextures.on.ca/xlDataVal13.html


Gord Dibben MS Excel MVP

 

[Jon]

Thank you for your responses, I forgot to mention I was using 07 Not sure
how much it matters, I will search to see where MS moved the name feature
with 07 hopefully they didnt change that functionality too much.

 

[Per Jessen]

Hi Gordon,

Thanks for clarifying that.

Regards,
Per