Home

Using Table Headers as ranges in Data Validation

%3CLINGO-SUB%20id%3D%22lingo-sub-1271167%22%20slang%3D%22en-US%22%3EUsing%20Table%20Headers%20as%20ranges%20in%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20Members%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20a%20table%20named%20%22Books%22.%20My%20table%20is%20very%20simple%20with%203%20columns.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EBook.ID%2C%20Book.Name%2C%20Book.Author%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EAll%20i%20want%20to%20do%20is%20to%20avoid%20duplicate%20values%20in%20Book.ID%20column%20of%20a%20table%20using%20data%20validation.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20simply%20used%20the%20formula%26nbsp%3B%3C%2FSPAN%3E%3CI%3E%3DCOUNTIF(Books%5BBook.ID%5D%2CBooks%5B%40%5BBook.ID%5D%5D)%3D1%3C%2FI%3E%3C%2FP%3E%3CP%3EI%20also%20used%20%22%20around%20table%20names.%26nbsp%3B%3CI%3E%3DCOUNTIF(%22Books%5BBook.ID%5D%22%2C%22Books%5B%40%5BBook.ID%5D%5D%22)%3D1%3C%2FI%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EBut%20it%20throws%20formula%20error%20window...%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EWhat%20am%20i%20doing%20wrong%20in%20this%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EMy%20table%20structure.%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22bbTable%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EBook.ID%3C%2FTD%3E%3CTD%3EBook.Name%3C%2FTD%3E%3CTD%3EBook.Author%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10001%3C%2FTD%3E%3CTD%3ESample%20Book%201%3C%2FTD%3E%3CTD%3ESample%20Author%201%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10002%3C%2FTD%3E%3CTD%3ESample%20Book%202%3C%2FTD%3E%3CTD%3ESample%20Author%202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10001%3C%2FTD%3E%3CTD%3ESample%20Book%203%3C%2FTD%3E%3CTD%3ESample%20Author%203%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1271167%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274213%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Table%20Headers%20as%20ranges%20in%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274213%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F604373%22%20target%3D%22_blank%22%3E%40prabutr28%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20using%20R1C1%20reference%20style.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(%24A%242%3AA2%2CA2)%26lt%3B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20also%20allow%20for%20growth%20of%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3EBR%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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

I also used " around table names. =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.IDBook.NameBook.Author
10001Sample Book 1Sample Author 1
10002Sample Book 2Sample Author 2
10001Sample Book 3Sample Author 3
1 Reply
Highlighted

Hi @prabutr28 

 

Try using R1C1 reference style.

 

=COUNTIF($A$2:A2,A2)<2

 

This will also allow for growth of the table.

 

Hope this helps.

BR,