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

 

[Peter_SSs]

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

The assumptions there are that
- the OP wants the book ids to simply be a sequential list of numbers (which could be right - we haven't been told one way or the other)
- it would be okay for a particular book to have a certain id today and a different id tomorrow (if any rows were subsequently inserted mid-table)

 

[LuisV805]

Gotcha. If you need static ID's that don't change, you can't use formulas. You need hard coded values. In that case, you can either enter them manually or use VBA to fill in blank values:

sub BookIDs_Populate

 

[LuisV805]

Sorry, hit enter too soon and now I can't edit that entry... so consider that an invalid entry... How's that for irony lol

Ok, anyways...

I recommend against manual entry if avoidable. I would use VBA to fill in blank cells automatically with the next sequential number. If the user MUST have the ability to enter these manually or looking to avoid VBA, then you can use Conditional Formatting to highlight duplicate entries. It won't outright stop the user from entering that value, but it will call out the discrepancy.

 

[Peter_SSs]

I recommend against manual entry if avoidable. I would use VBA to fill in blank cells automatically with the next sequential number.

As far as we know, the OP is not trying to enter a list of sequential numbers..

All i want to do is to avoid duplicate values in Book.ID column

you can use Conditional Formatting to highlight duplicate entries. It won't outright stop the user from entering that value,

.. but Data Validation, as requested, can stop the duplicate entry.

.. avoid duplicate values in Book.ID column of a table using data validation.