duplicate entries

[Guest]

Hi.
I'm trying to create a driver schedule in Excel. In each of 53 sheets, I
select the driver's name in cell C3 using a drop down box (Data Validation
List). I want Excel to tell me if I am selecting a driver that has already
been selected in another sheet (same cell C3). Is there any easy way to do
this?

Any suggestions would be greatly appreciated....

 

[Dave Peterson]

Maybe you could visit Debra Dalgleish's site.

http://www.contextures.com/xlDataVal03.html

The title of that page is:
Excel -- Data Validation -- Hide Previously Used Items in Dropdown

Maybe not being able to select it is as good as telling you it was already
selected.

 

[Guest]

Thanks Dave.
Unfortunately, that won't work for my purposes. I want the user to be able
to choose the driver again if necessary. I simply want the user to be made
aware that this particular driver has already been selected.

 

[Dave Peterson]

I think I'd use a helper worksheet (hidden???).

Fill it with just formulas that point at the other 53 cells.

=sheet1!c3
=sheet2!c3
=sheet3!c3
....

for 53 rows.

Then you could use a formula like:

=if(countif('hiddensheet'!a:a,c3)>1,"Used before","")

If you don't like those 0's appearing in formulas when you're returning the
value from an empty cell:

=if(sheet1!c3="","",sheet1!c3)

You could even show how many times it's been used:

="This has been used: " & countif('hiddensheet'!a:a,c3) & " times total."

(subtract one if you don't want to include that sheet in the count.


Then in