Data Validation - Duplicate Entries

[schang_825]

Hi all,

I have a schedule for a move-in of a new condominium that currently does not allow for scheduling of the same unit twice. However, I want to allow the penthouse units to have multiple move-in slots. My current formula is:

=COUNTIF($D$10:$GC$30,D10)=1

How can I change it to allow the penthouse units (4601-4809) to be scheduled more than once?

Thanks!

 

[rdw72777]

How is your data setup? What does your formula evaluate to, it looks like you want to get to a True/False value? What is in cell D10?

 

[schang_825]

Thanks for your quick response, Ryan.

I have the dates going across in Row 8, with the times going down in Column B. In D10 to GC30, the unit numbers will be entered. If a unit is scheduled twice, a message box will pop up notifying the person that this unit has already been scheduled.

What I want to modify, is if that unit is a penthouse unit (eg: unit 4803), then that unit can be entered twice. Does that help?

 

[rdw72777]

Ah I see. Are you doing this through a data validation range on the rnage D10:GC30? If so, I wounder if your formula could ignore the penthouses altogether.

=OR(COUNTIF($D$10:$GC$30,D10)=1,D10=4801,D10=4802,D10=4803, D10=4804, D10=4805,D10=4806, D10=4807,D10=4808,D10=4809)

Its ugly but maybe it works?

 

[schang_825]

Thanks for your quick response! I actually figured out a solution right before reading yours:

=IF(OR(D10=4802,D10=4803,D10=4804,D10=4805,D10=4806,D10=4807,D10=4808,D10=4809),D10,COUNTIF($D$10:$GC$30,D10)=1)