lock cell based on another cell

[Arie Bos]

I know there are several posts on this subject, however I wanted to understand the following:

I have a protected sheet. There is one cell I'd like to open/lock depending if another cell has a value or not. I prefer not to use VBA, as the spreadsheet is the source for an on-line tool on our website, and I do not know if xlsm also works for that purpose.
Is it possible to do this without VBA? I tried conditional formatting, but that does not show the 'Protection' tab. I tried using data validation, but that does not much at all.

 

[Ottsel]

Hmmm, I feel like data validation is the way to go, but you've tried this already, so perhaps Conditional Formatting is what you need:
Select New Rule followed by 'use a formula to determine which cells to format'. Then, use '=ISNUMBER(A1)' assuming A1 is the one you want to target then select the color you want to fill. Although it doesn't guarantee security it gives the users a visual of whether you can input there or not.

You can use the '=ISNUMBER(A1)' approach with Data Validation. Just pick Custom and Allow dropdown, then in the Formula field use 'ISNUMBER(A1)' again, assuming A1 is what you're after; then you can navigate to the Error Alert section to set a custom message for your users. This will allow the select cell or cells input only if the condition is met, which would be if A1 contains a value.

Hopefully I understood this correctly and this helps.

 

[Peter_SSs]

I tried using data validation, but that does not much at all.

You did not give any details of how you tried this.

For the sheet below, Data Validation was set up in cell A1 like this.

23 08 23.xlsm
	A	B
1
DV

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=B1<>""

This does not allow entry into A1 until B1 has an entry. Is that what you were trying to do?

(Of course in this example A1 & B1 would need to have the 'Locked' setting removed before the sheet was protected)

 

[Arie Bos]

You did not give any details of how you tried this.

For the sheet below, Data Validation was set up in cell A1 like this.

View attachment 97568

23 08 23.xlsm
	A	B
1
DV

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=B1<>""

This does not allow entry into A1 until B1 has an entry. Is that what you were trying to do?

(Of course in this example A1 & B1 would need to have the 'Locked' setting removed before the sheet was protected)

Thank you Peter,
II tried this , but did it again as you show here, just to be sure I did it right.
The data validation error box pops up when there is no number in B1, but also when there is a number in B1, so always , both in protected as unprotected sheet mode.

 

[Peter_SSs]

The data validation error box pops up when there is no number in B1, but also when there is a number in B1, so always , both in protected as unprotected sheet mode.

Are you sure that B1 is empty and doesn't perhaps have a space character in it?

When the sheet is unprotected and you get the pop-up trying to enter in A1 when B1 appear empty, cancel the pop-up and then put this formula in a vacant cell
=LEN(B1)
What does that return?

 

[Arie Bos]

Are you sure that B1 is empty and doesn't perhaps have a space character in it?

When the sheet is unprotected and you get the pop-up trying to enter in A1 when B1 appear empty, cancel the pop-up and then put this formula in a vacant cell
=LEN(B1)
What does that return?

Thank you again. I made a silly mistake.... it works perfectly...

 

[Peter_SSs]

You're welcome. Glad you got it sorted. Thanks for the confirmation.