Using Table Headers as ranges in Data Validation

[prabutr28]

Hi Members,
I have a table named "Books". My table is very simple with 3 columns.
Book.ID, Book.Name, Book.Author
All i want to do is to avoid duplicate values in Book.ID column of a table using data validation.
I simply used the formula "=COUNTIF(Books[Book.ID],Books[@[Book.ID]])=1".

But it throws formula error window...

What am i doing wrong in this?

My table structure.

Book.ID	Book.Name	Book.Author
10001	Sample Book 1	Sample Author 1
10002	Sample Book 2	Sample Author 2
10001	Sample Book 3	Sample Author 3

 

[Fluff]

Hi & welcome to MrExcel
I don't think you can you table references in data validation.
Assuming Book.ID is in A1 try
=COUNTIF(A$2:A2,A2)=1

 

[prabutr28]

I also used " around table names. =COUNTIF("Books[Book.ID]","Books[@[Book.ID]]")=1
Still doesnt work.

 

[Fluff]

See post#2

 

[prabutr28]

Hi & welcome to MrExcel
I don't think you can you table references in data validation.
Assuming Book.ID is in A1 try
=COUNTIF(A$2:A2,A2)=1

The Post here has an example of using table headers as ranges for a different scenerio.

Validation Dropdown lists based on Table Columns ranges | The Excel Factor

There are now two easier ways to define Validation Dropdown lists based on Table Columns ranges. In the past you had to set up range names.

www.theexcelfactor.com

 

[Fluff]

That is using the the table header as a named range & then using the Indirect function. It's not the same as what you are trying to do.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this data validation in your table. It will not let me enter 10001 or 10002 into cell A4.

20 04 01.xlsm
	A	B	C
1	Book.ID	Book.Name	Book.Author
2	10001	Sample Book 1	Sample Author 1
3	10002	Sample Book 2	Sample Author 2
4		Sample Book 3	Sample Author 3
Books

Cells with Data Validation
Cell	Allow	Criteria
A2:A4	Custom	=COUNTIF(INDIRECT("Books[Book.ID]"),INDIRECT("Books[@[Book.ID]]"))=1

 

[jasonb75]

try
=COUNTIF(A$2:A2,A2)=1

Without the table I think it would be preferable to use
=COUNTIF(A:A,A2)=1
if you only look at the rows above, the rule could be broken by going back to edit a previous entry.

 

[Fluff]

if you only look at the rows above, the rule could be broken by going back to edit a previous entry.

Very good point

 

[LuisV805]

I suggest not using data validation here. Instead, let the ID column enumerate itself via formula. Enter this in the Book.ID Column:

=ROW()-ROW(tbl_Books[#Headers])

Just replace tbl_Books with the name of your actual table. You can always customize the formula and number formatting from there.