• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

How to validate unique "compound" entries?

Hello - I have a data input spreadsheet where the user enters project budget allocations in rows where 3 of the cells must be a unique combination of company code, site location, and service type.

There could be any number of rows, but those 3 values, once entered on a given row, must not repeat on any other row.

How do you do this?

Thanks
0
mlagrange
Asked:
mlagrange
1 Solution
 
NBVCCommented:
Assuming your range to apply the validation is A2:C5, then select that range and go to Data Validation, select Custom from Allow menu and enter formula:

=COUNTIFS($A$2:$A$5,$A2,$B$2:$B$5,$B2,$C$2:$C$5,$C2)=1

Click Ok.

Now, if you enter say a, b, c in A2:C2 you won't be able to enter that combo in any other row.
0
 
mlagrangeAuthor Commented:
Works like a charm - Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now