• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting - lock cells based on other cells values

acataleptic

New Member
Hi there,

I am hoping you’d be willing to assist me with the problem I have. We have a Vacation Planner where we need to lock all other cells on the column, when a cell is marked with X. In the attached file, let’s say that Employee4 has marked January 7 with an X (cell# H10) and saved the file. I’d like to make sure that no one can book that day for the rest of the month- make all the other cells in Column H for that month unavailable.

I had tried to use Conditional Formatting but didn’t get to make it work. Any assistance will be highly appreciated. Thank you so much in advance.
 

Attachments

AliGW

Active Member
By conditional formatting, I guess you really mean data validation (which is entirely different, of course - you cannot prevent data entry with conditional formatting).

Try this custom DV rule:

=COUNTIF(H$5:H$26,"x")=0
 

NARAYANK991

Excel Ninja
By conditional formatting, I guess you really mean data validation (which is entirely different, of course - you cannot prevent data entry with conditional formatting).

Try this custom DV rule:

=COUNTIF(H$5:H$26,"x")=0
Hi ,

Shouldn't that be :

=COUNTIF(H$5:H$26,"x") = 1

Narayan
 

AliGW

Active Member
No - that would allow data entry if any one of the cells in the range contains an "x" - the OP wants to prevent data entry if ANY of the cells contain X. So, in order to ALLOW entry, they must ALL be empty, hence we need a count of 0.

Try it. I have tested the solution on the OP's data. :)
 

bosco_yip

Excel Ninja
This is the OP's requirement : "to lock all other cells on the column, when a cell is marked with X "

My understand that the column range should allow only 1 (one) "x" entry

So, I support Narayan's DV rule formula : =COUNTIF(H$5:H$26,"x")=1

Remark : Ali's formula =COUNTIF(H$5:H$26,"x")=0 is equal to Select H5:H26 >> DV rule formula enter ="x"

Regards
Bosco
 

acataleptic

New Member
Thank you all so much for the responses and the suggestions. Unfortunately, none seems to be working.

When I input the code in cell H5, I get a “Circular Reference Warning”. I read the error and pasted the code on a different cell- H2 to bypass this, but then nothing happens as I can still edit cells H5 to H26. I need to prevent that. I need to prevent multiple employees booking the same day as their vacation.

I do apologize if I was not clear on my request initially.
 

acataleptic

New Member
that's embarrassing :D

Thank you so much Ali, it works! I can't thank you all enough for helping out of this bind. Wish you all the best :)
 
Top