How to validate data entries to be unique within an array

D

Dwight at Boeing

I am using a worksheet to track channel assignments for a data acquisition
system. The sheet has an array of cells which define by rows the input cards
in the system. The columns are the channels for each card. The entries are
short mnemonics which identify which signal is connected to a particular
channel. I would like to make sure that the same signal name cannot be
entered twice in the array. This validation would assist in relocating
signals if the system has to be reconfigured and would assist in assigning
signal names to new proposed signals.

Can an array of cells be selected and a validation check applied to make
sure that only one occurence of a data entry is made within the array?
 
B

Bernie Deitrick

Dwight,

Select your cells, then use Data | Validation... Choose "Custom" and use a
formula like

=COUNTIF($A$1:$H$10,A1)<=1

This formula should be written with the address of the entire range in $$
format as the first argument, and the address of the activecell (the cell
that is not shaded within the selection) as the second.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top