Structured references in custom Data Validation expression?

[Guest]

I'm very keen to know how could I enter a Data Validation expression using
tables.

I have two sheets with two tables. In a certain column of the first I need
to restrict the values one can enter to the values entered in a column in the
other table.

So for ColumnX in Table1, I'd like to set a custom data validation
expression like:
=Table2[ColumnY]
to tell only values in the ColumnY column are enabled to be entered in
ColumnX.

Is there a way to do this or isn't this stuff supported?

 

[Peo Sjoblom]

Select all necessary cells in column X, do data>validation, allow>list and
select the cells in column Y, deselect dropdown

 

[T. Valko]

Try this:

Table2 on sheet2

Give Table2 a defined name like, say, oh, I don't know, maybe Table2. <g>

Then, select the cells you want to validate. Assume this range is A1:A5.

Data Validation
Allow: Custom
Formula: =OR(A1=INDEX(Table2,,n))

Where n = the column number of Table2 you're interested in

Biff

 

[Guest]

Hi,

Thanks for your answer.

Unfortunately the referenced cells are on other sheet. On the other hand,
I'd like to ensure that by expanding Table2, the newly included rows will be
available for choosing as a value in Table1. That's why I wouldn' like to use
a named range. Specifically the best would be to reference a column in a
table without the need of turning its cells into a range or list.

krank

Select all necessary cells in column X, do data>validation, allow>list and
select the cells in column Y, deselect dropdown


--
Regards,

Peo Sjoblom

I'm very keen to know how could I enter a Data Validation expression using
tables.

I have two sheets with two tables. In a certain column of the first I need
to restrict the values one can enter to the values entered in a column in
the
other table.

So for ColumnX in Table1, I'd like to set a custom data validation
expression like:
=Table2[ColumnY]
to tell only values in the ColumnY column are enabled to be entered in
ColumnX.

Is there a way to do this or isn't this stuff supported?

 

[Peo Sjoblom]

Did you try Biff's solution? If you have the list/values in another sheet
with regards to the validation you need to give it a name or it won't work


--
Regards,

Peo Sjoblom

Hi,

Thanks for your answer.

Unfortunately the referenced cells are on other sheet. On the other hand,
I'd like to ensure that by expanding Table2, the newly included rows will
be
available for choosing as a value in Table1. That's why I wouldn' like to
use
a named range. Specifically the best would be to reference a column in a
table without the need of turning its cells into a range or list.

krank

Select all necessary cells in column X, do data>validation, allow>list
and
select the cells in column Y, deselect dropdown


--
Regards,

Peo Sjoblom

I'm very keen to know how could I enter a Data Validation expression
using
tables.

I have two sheets with two tables. In a certain column of the first I
need
to restrict the values one can enter to the values entered in a column
in
the
other table.

So for ColumnX in Table1, I'd like to set a custom data validation
expression like:
=Table2[ColumnY]
to tell only values in the ColumnY column are enabled to be entered in
ColumnX.

Is there a way to do this or isn't this stuff supported?

 

[Guest]

Thanks to you, too.

This is still not the desired solution but based on your answers I guess I
cannot do what I exactly wanted to do but the old named ranges solution. It
was good if this cool new feature (I mean the structured references) worked
even with data validation.

Try this:

Table2 on sheet2

Give Table2 a defined name like, say, oh, I don't know, maybe Table2. <g>

Then, select the cells you want to validate. Assume this range is A1:A5.

Data Validation
Allow: Custom
Formula: =OR(A1=INDEX(Table2,,n))

Where n = the column number of Table2 you're interested in

Biff

I'm very keen to know how could I enter a Data Validation expression using
tables.

I have two sheets with two tables. In a certain column of the first I need
to restrict the values one can enter to the values entered in a column in
the
other table.

So for ColumnX in Table1, I'd like to set a custom data validation
expression like:
=Table2[ColumnY]
to tell only values in the ColumnY column are enabled to be entered in
ColumnX.

Is there a way to do this or isn't this stuff supported?

 

[T. Valko]

I'd like to ensure that by expanding Table2, the newly

included rows will be available for choosing
as a value in Table1.

In that case you simply need to make Table2 a dynamic named range.

http://contextures.com/xlNames01.html#Dynamic

Biff

Hi,

Thanks for your answer.

Unfortunately the referenced cells are on other sheet. On the other hand,
I'd like to ensure that by expanding Table2, the newly included rows will
be
available for choosing as a value in Table1. That's why I wouldn' like to
use
a named range. Specifically the best would be to reference a column in a
table without the need of turning its cells into a range or list.

krank

Select all necessary cells in column X, do data>validation, allow>list
and
select the cells in column Y, deselect dropdown


--
Regards,

Peo Sjoblom

I'm very keen to know how could I enter a Data Validation expression
using
tables.

I have two sheets with two tables. In a certain column of the first I
need
to restrict the values one can enter to the values entered in a column
in
the
other table.

So for ColumnX in Table1, I'd like to set a custom data validation
expression like:
=Table2[ColumnY]
to tell only values in the ColumnY column are enabled to be entered in
ColumnX.

Is there a way to do this or isn't this stuff supported?

 

[Guest]

Great!

Thx again,
kr/

I'd like to ensure that by expanding Table2, the newly
included rows will be available for choosing
as a value in Table1.

In that case you simply need to make Table2 a dynamic named range.

http://contextures.com/xlNames01.html#Dynamic

Biff

Hi,

Thanks for your answer.

Unfortunately the referenced cells are on other sheet. On the other hand,
I'd like to ensure that by expanding Table2, the newly included rows will
be
available for choosing as a value in Table1. That's why I wouldn' like to
use
a named range. Specifically the best would be to reference a column in a
table without the need of turning its cells into a range or list.

krank

Select all necessary cells in column X, do data>validation, allow>list
and
select the cells in column Y, deselect dropdown


--
Regards,

Peo Sjoblom



I'm very keen to know how could I enter a Data Validation expression
using
tables.

I have two sheets with two tables. In a certain column of the first I
need
to restrict the values one can enter to the values entered in a column
in
the
other table.

So for ColumnX in Table1, I'd like to set a custom data validation
expression like:
=Table2[ColumnY]
to tell only values in the ColumnY column are enabled to be entered in
ColumnX.

Is there a way to do this or isn't this stuff supported?

 

[DanL]

I have the same question, and no one has answered it yet. Can you use a
structured table reference instead of a named range in order to perform data
validation? In other words, instead of creating a table and having to
manually create a named range based on the table, can't I just use a formula
like =DeptSales[#Data] for a list data validation (where "DeptSales" is the
name of the table)? I get an error message when I try this...

 

[DanL]

Well, I just answered my own question. It is possible!

Put the following in some cell in your worksheet (for instance, A1):
'INDEX(TableName[#Data],,n)

where "TableName" is the name of your table and "n" is the column number of
your table that you want to use as the validation list. Note the single
quote at the beginning.

Now select a (different) cell where you want to apply data validation, and
perform the data validation steps as usual. From the data validation
settings menu, enter the following in the "Source:" field:

=indirect(A1)

where A1 is the cell that has your table reference as mentioned earlier.

Voila! You now have a dropdown that has an updated selection list based on
your table.

I have tried putting the table reference directly into the "Source:" field
instead of pointing to a cell that has the same text, but for some reason it
won't work.

Is there a better solution?

I have the same question, and no one has answered it yet. Can you use a
structured table reference instead of a named range in order to perform data
validation? In other words, instead of creating a table and having to
manually create a named range based on the table, can't I just use a formula
like =DeptSales[#Data] for a list data validation (where "DeptSales" is the
name of the table)? I get an error message when I try this...

Thanks to you, too.

This is still not the desired solution but based on your answers I guess I
cannot do what I exactly wanted to do but the old named ranges solution. It
was good if this cool new feature (I mean the structured references) worked
even with data validation.

 

[T. Valko]

Is there a better solution?

Well, IMHO just use standard range references. I am not a fan of structured
references.


--
Biff
Microsoft Excel MVP

Well, I just answered my own question. It is possible!

Put the following in some cell in your worksheet (for instance, A1):
'INDEX(TableName[#Data],,n)

where "TableName" is the name of your table and "n" is the column number
of
your table that you want to use as the validation list. Note the single
quote at the beginning.

Now select a (different) cell where you want to apply data validation, and
perform the data validation steps as usual. From the data validation
settings menu, enter the following in the "Source:" field:

=indirect(A1)

where A1 is the cell that has your table reference as mentioned earlier.

Voila! You now have a dropdown that has an updated selection list based
on
your table.

I have tried putting the table reference directly into the "Source:" field
instead of pointing to a cell that has the same text, but for some reason
it
won't work.

Is there a better solution?

I have the same question, and no one has answered it yet. Can you use a
structured table reference instead of a named range in order to perform
data
validation? In other words, instead of creating a table and having to
manually create a named range based on the table, can't I just use a
formula
like =DeptSales[#Data] for a list data validation (where "DeptSales" is
the
name of the table)? I get an error message when I try this...

Thanks to you, too.

This is still not the desired solution but based on your answers I
guess I
cannot do what I exactly wanted to do but the old named ranges
solution. It
was good if this cool new feature (I mean the structured references)
worked
even with data validation.

 

[PETROCJC]

All: I found an option that comes closer to the original intent of the
question.

Given a column of data named "ColumnX" in a table named "Table1", we have
all discovered that entering...

=Table1[[#Data],[ColumnX]]

.... in the "Source:" field of the Data Validation list dialog box will not
work.

However, the same syntax works for named ranges. For example, create a new
named range called "Test1" and enter...

=Table1[[#Data],[ColumnX]]

.... in the "Refers to" field.

Now in Data Validation, you can reference the named range "Test1" in the
"Source" field, using the syntax...

=Test1

It you add additional rows to "Table1", with new values in "ColumnX", the
data validation list is expanded accordingly. Also, sorting "ColumnX" in
"Table1" reorders the data validation list.

-------------

I have the same question, and no one has answered it yet. Can you use a
structured table reference instead of a named range in order to perform data
validation? In other words, instead of creating a table and having to
manually create a named range based on the table, can't I just use a formula
like =DeptSales[#Data] for a list data validation (where "DeptSales" is the
name of the table)? I get an error message when I try this...

Thanks to you, too.

This is still not the desired solution but based on your answers I guess I
cannot do what I exactly wanted to do but the old named ranges solution. It
was good if this cool new feature (I mean the structured references) worked
even with data validation.