solving countifs function working in cell failing in validation

[elzein]

Hi every one
i am working in column validation validation to avoid duplicates
the countifs formula worked fine in cell
I am using table. and 3 count ifs
the logic, if task no is the same and resource wbs is steel the resource type can not accept duplicated type of steel
but when pasting it inside validation
it gave me an error
please help me to fix it thank you


the formula
=COUNTIFS([Resourse WBS],"Steel",[Task ID],[@[Task ID]],[Resource Type],[@[Resource Type]])<=1

 

[Fluff]

Hi & welcome to MrExcel.
You cannot use structured references in data validation, so you need to change them to normal ranges.

 

[Peter_SSs]

Try this in the Resource Type column Data Validation. Check the table name

elzein.xlsm
	A	B	C
1	Task ID	Resourse WBS	Resource Type
2	6	Steel	STEEL P 16
3	6	Steel	STEEL P 17
4	6	Material
5	6	Equipment
6	6	Labour
7	6	Labour
8	6	Steel	vx
9	6	Steel	vxy
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C2:C9	Custom	=COUNTIFS(INDIRECT("Table1[Resourse WBS]"),"Steel",INDIRECT("Table1[Task ID]"),INDIRECT("Table1[@[Task ID]]"),INDIRECT("Table1[Resource Type]"),INDIRECT("Table1[@[Resource Type]]"))=1

 

[elzein]

COUNTIFS(INDIRECT("Table1[Resourse WBS]"),"Steel",INDIRECT("Table1[Task ID]"),INDIRECT("Table1[@[Task ID]]"),INDIRECT("Table1[Resource Type]"),INDIRECT("Table1[@[Resource Type]]"))=1

thanks for help sorry for late reply , i was trying hard to figure it out and i am stuck
i will feed back after check

 

[elzein]

1

the validation formula was accepted, but any input in cell was rejected
i will simplify the need
i have 2 sheets for products, 1st has ID and name, this can have multiple duplicated IDs and names
the second sheet, can accept multiple IDs, but within each ID should accept ONE product name only
thats all i need to reach
may be simplification helps

 

[Fluff]

How about

=COUNTIFS($A:$A,$A2,$B:$B,$B2)=1