Protect cells conditionally?

Question

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?

Answer 1

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.