Conditional formatting - lock cells based on other cells values

[acataleptic]

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.

 

[AliGW]

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]

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]

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]

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]

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.

 

[AliGW]

The formula (rule) goes into the DV dialog, not the cell.

 

[acataleptic]

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

 

[AliGW]

I’m glad we got there in the end! :D