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.

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

"Jon" skrev i meddelelsen
...
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.

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.

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

On Tue, 29 Apr 2008 22:06:52 +0200, "Per Jessen" wrote:

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

"Jon" skrev i meddelelsen
...
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.

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


On Tue, 29 Apr 2008 22:06:52 +0200, "Per Jessen" wrote:

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

"Jon" skrev i meddelelsen
...
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.

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.
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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


On Tue, 29 Apr 2008 22:06:52 +0200, "Per Jessen"
wrote:

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

"Jon" skrev i meddelelsen
.. .
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.

Hi Gordon,

Thanks for clarifying that.

Regards,
Per

"Gord Dibben" <gorddibbATshawDOTca skrev i meddelelsen
...
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

On Tue, 29 Apr 2008 22:06:52 +0200, "Per Jessen"
wrote:

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

"Jon" skrev i meddelelsen
.. .
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.