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,
Last edited by NBVC; 07-14-2010 at 11:13 AM.
Assuming table is called Tbl1 (T1 can't be a table name)...
Then try Data|Validation|List: =INDEX(Tbl1,0,2)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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,
See attached.. Yellow cell has the validation.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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,
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)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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,
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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,
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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks. That helped.
Regards,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks