7

I've a sheet that is basically going to serve as a collaborative database for some old books. Some of these books have been reprinted, while others have not. One of the things I'm trying to keep track of is the title of the reprint (as sometimes it's different from the original for various reasons).

My column, "G", is a simple Yes/No dropdown that has a header asking if there's a reprint. "H" is the field that asks for the title. By default, I want "H" to be protected ("G" being NULL by default). If a user sets "G" to "No", it should remain protected. If a user sets "G" to "Yes" (meaning there is one), it should unlock "H" and let them drop a title in.

I can figure out conditional formatting (change colors and whatnot), and cell protection, but I can't seem to put two and four together to get conditional protection. Closest I found was this: Protecting Cells Based on Contents of Other Cells in Google Sheets.

It seems similar. I'd thought about working back through the code but I haven't touched actual code in quite many years (didn't go the software route, unfortunately), so that could be a frustrating dead end.

Is it possible to conditionally lock or unlock a cell in Google Sheets?

Is it a built-in tool, or am I going to have to throw down some code?

Community
  • 1
  • 1
The ITea Guy
  • 225
  • 1
  • 3
  • 8

1 Answers1

8

Yes you can set the validation for the entire column of H. For the rule - select custom formula and enter in this:

=if(G1="Yes"),true,false)

Then choose the radio button for "reject input" and click save.

Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
  • Ah! That appears to have done it (or rather, =if((G1="Yes"),true,false) does). Where is further documentation on this kind of action? I realized as I was testing this out that A) I don't know what the ,true,false parts are actually doing and B) I'd like to further improve the database by not allowing some fields to be altered unless others are filled in (with a variable string). I tried guessing if(B2!=""),true,false plus some others but no luck. – The ITea Guy Feb 07 '16 at 22:33
  • Ah, I found this: https://support.google.com/docs/answer/3093290?hl=en Wrap that in NOT() inside of an IF() and I've got gold. Still not sure what the true,false bit does, but it seems to permit or deny writing in a cell. One last bit: Can a cell have more than one validation rule? I.E. you can only write in E4 IF E2 is filled AND what you write is bigger than, say, 2600. (all random data examples) – The ITea Guy Feb 07 '16 at 23:06
  • Yes you can create multiple if statements and you can point to different cells - you can also write 'AND' statements something along the lines of =if(AND(istext(A1),G1="Yes"),true,false) or =if(AND(isblank(A1),G1="Yes"),true,false) – Aurielle Perlmann Feb 08 '16 at 00:18
  • the true false part is to allow the validation to operate, it operates on a 'true' result – Aurielle Perlmann Feb 08 '16 at 00:19