Absolute Table References

[foolishpiano]

Hello everyone!

I am working with a table, which is new to me, and I'm trying to create absolute references to identify the first instance of a duplicate value. I found the formula to identify the first duplicate value from Exceljet found here: https://exceljet.net/formula/flag-first-duplicate-in-a-list, and I've been reviewing the absolute reference guide found here: https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/, as well as the information from previous posts on this board (found here: https://www.mrexcel.com/forum/excel...ables-structured-references-absolute-ref.html and here: https://www.mrexcel.com/forum/excel-questions/455143-absolute-cell-reference-when-using-tables.html).

When using just a normal reference, the formula found on Exceljet works; for my purpose the formula is:

=IF(COUNTIF($AA$3:$AA$6880,AA3)>1,IF(COUNTIF($AA$3:AA3,AA3)=1,"x","xx"),"")

When I try to make absolute table references, my code is as follows, which returns blanks:

=IF(COUNTIF([@Property]:[@Property],[Property])>1,IF(COUNTIF([@Property]:[Property],[Property])=1,"x","xx"),"")

I would greatly appreciate all of your help with this. I'm sure I'm missing something simple.

Thank you all!

 

[Marcelo Branco]

What is the table name?

M.

 

[RoryA]

I think you mean:

=IF(COUNTIF([Property],[@Property])>1,IF(COUNTIF(INDEX([Property],1):[@Property],[@Property])=1,"x","xx"),"")

 

[foolishpiano]

What is the table name?

M.

Hi Marcelo! The table name is: ProcessedFamilies

Thank you!

 

[foolishpiano]

I think you mean:

=IF(COUNTIF([Property],[@Property])>1,IF(COUNTIF(INDEX([Property],1):[@Property],[@Property])=1,"x","xx"),"")

Rory! Thank you so much; that worked! Interesting that we use the INDEX function for the table; why do we need to do that?

Thank you again for all of your help!

 

[RoryA]

There isn't a symbol for locking the row number in structured references, so you need to use something like INDEX.