Data validation on a Table column

[agastyakrishna]

Hi,

I am trying to validate the contents of a cell based on a Table column.

I have a table, T1 with 3 columns. I want cell A10 to have a data validation based on only the 2nd column of the Table. I tried setting the data validation as a list with the source as "=T1[[#Data],[Column2]]" but it throws an Error. Can you let me know what I am doing wrong here.

I am using Excel 2007.

Regards,

[NBVC]

Assuming table is called Tbl1 (T1 can't be a table name)...

Then try Data|Validation|List: =INDEX(Tbl1,0,2)

[agastyakrishna]

Hi

Tried your suggestion, but its still giving the same error, "The formula you typed contains an error". It is selecting the correct column and cell, but the error is coming.

Regards,

[NBVC]

See attached.. Yellow cell has the validation.

[agastyakrishna]

Hi,

Thanks for responding promptly.

I am creating a Data Table using Insert | Data Table. I am not using the data grouping into a Table. So how do I access a data table column. I want to validate the Cell contents against a Datatable column.

Sorry for not having clarified correctly.

Regards,

[NBVC]

When you say you want to validate what exactly do you mean? You want a list of all those items in column 2 to choose from? Do you want to compare something? Please advise.

Also is it Table or Pivot Table... there is no "Data Table"

an attachment would be useful too.. showing what you have and what you want (non-confidential data only)

[agastyakrishna]

In Excel 2007, I am doing the operation, Insert -> Table. This opens a dialog where I select the Header row.

I will select a cell 'A10' and want to validate against a column in this table. So when I select the cell 'A10' it should show a drop-down of all the values in the column.

Hope this helps.

Regards,

[NBVC]

Not sure if we are on the same page yet...

But say your table column you want a list of is in column C... then the data validation List formula would be:

=OFFSET($C$1,1,0,COUNTA($C:$C)-1,1)

This makes a dynamic list that expands as you add to column C.

is that what you want?

If not, please do post a sample workbook.

[agastyakrishna]

Hi,

I am attaching a file. Using the approach you suggested I am able to add the validation to another cell in the same worksheet.

Is there any way of adding the data validation in a separate worksheet. Pls have a look at the "Sheet2" in the attached file.

Regards,

[NBVC]

You would need to create a defined dynamic range name first and refer to it in the data validation..

So go to Formulas|Define Name, enter a name like: Tbl in the name field.

Enter formula: =OFFSET(Sheet1!$C$3,1,0,COUNTA(Sheet1!$C:$C)-1,1) in the refers to field.


Then in Data Validation you enter: =Tbl

[agastyakrishna]

Thanks. That helped.

Regards,