Multi Column Data Validation in Excel Table

[TheFrog]

Hi Everyone,

I have an Excel table. I am trying to perform a multi-column data validation. I am trying to use the COUNTIFS function like so:
=COUNTIFS([Supplier],C2,[Supplier_Code],D2)<=1

The goal is to ensure that the data entered is not a duplicate. The combination of [Supplier] and [Supplier_Code] is the basis of validation. The Supplier name is provided via data validation drop-down list in Column C, and the Supplier Code is entered (manually) into Column D.

When I use the formula in a calculated cell it works perfectly. When I try to use it as a data validation custom formula I receive an error: 'There's a problem with this formula........ Not trying to type a formula?......'. I have tried to get around this by using a helper column with the Supplier's name in it and applying the validation formula to both columns at the same time - no difference made, same error.

This is seemingly something I am not referencing properly when using the table to reference itself for the validation.

Can anyone steer me in the right direction?

Any help much appreciated.

Cheers

The Frog

 

[mart37]

Validation is an old function in Excel. It does not works with references you used.
Give the cells under the colum title a range name.
Use the range name in your validation formula.

 

[mart37]

Example: =COUNTIFS(Supplier,$C2,Supplier_code,$D2)<=1

 

[TheFrog]

Thanks for the feedback. I appreciate it.

I'll do the old style dynamic named ranges to overlap the table columns.

Cheers

The Frog