Google Sheets - Conditionally Lock Cells Based on Other Values

[@agugi]

Did you know that people like you, are great. Thank you very much.

[@TheMobius10]

Hello! Thank for this! Helpful info. Is there a way to also make it so that a user cannot delete the info that is already in the cell at that point? It works for rejecting input, but i want to use it to reject new input AND reject deleting of the content of the cell.

[@madhurapataki2428]

How to lock a cell based on multiple values in a particular cell? Can you please help me with this. Like if status is pending and shipped both should be allowed

[@nice6585]

/Seems like this does not work if the cell you are supposed to lock is a drop down list :( is there any alternative?

[@MaryJeyaseeli-j2j]

same doubt...did u get a solution?

[@jflying2167]

I have multiple drop downs made from data validation "list from a range". How can I retain this and also prevent people from engaging the other drop downs until the first on is selected?

[@SUR-NI-VARA]

Hi, Can I have similar validation based on system date>=today(), Reject data entry, I tried with formula at refering to cell with a text output as "Lock" =if(today()>=(2022,31,10),"Lock","") it could work, but gives a warning message at cell range, However I want to hide the (Top right red trangle "Error" validation mesage warning"), as I do not want user to know that the cell range has a validation in it, can I have multiple validation refereing t same range!! awaiting on your solution, thanks in advance

[@ashishdonda9589]

the trick that you used for drop down list open & close worked fine as I wasn't able to add new entries but it had one problem that is I was able to delete old entries using Backspace or Delete button

[@regretnowdoitlater2936]

same as mine

[@keyurshah679]

Not exactly in Google sheet but in Excel, can we achieve following Can we have method to automatically "unlock" (for any operation) a otherwise "locked cell" in protected sheet depending on value of another cell ? Let's say I have a worksheet in which all the cells are protected in column O. My column D has drop down menu to select from @ 58 list. What I want to achieve is if someone select from drop-down "subtotal" in any row in column D, respective cell in that row in column O shall be unlocked for subtotal. How we can achieve this. Looking forward and thanks in advance.

[@lei.zacarias03]

looking for this too❤❤❤❤

[@danielcunha2039]

Thank you. That's exactly what I looking for, but I have one question: How can I not ignore the blanks in the conditionals?

[@sigrunpaust2396]

Thank you, great video. Is there a way to do this with a checkbox in the conditional cell? Like if the checkbox is checked (=true) then the other cell is locked? I'm having trouble with this. Thank you for any thoughts.

[@2ale]

is that possible using check boxes? im trying to do that but it changes the check boxes with true or false values when i add a data validation

[@zaymarhenriquez-sedra2720]

Hello thanks - it worked great but what is I need two values Shipped and Delivered for example. I tried to use formula with OR but does not work. Please can you help me?

[@Graphiculator]

Thank you for the Tutorial. This is really helpful. What about if the Cell range already has a Data validation. Like a Dropdown menu or Check box. Is there any solution for this.

[@ProlificOaktree]

Not that I can think of.

[@emilygill8019]

@@ProlificOaktree sad

[@nouks81]

Thank you for this. I just tried it and I am wondering if there's a way to not show the error on data that was previously entered. I'm placing an order as a group but I want to reject changes once the order is set to "closed" without it showing the warning for the preexisting values. Thank you!

[@souravdebnath7064]

Thanks.. thats what i looking for some times.. thank you so much..

[@kulwindersingh5840]

Good morning sir, I just want to restrict color range in google sheet, so that no one will be able to change colour of sheet, only owner can change color of sheet. Is it possible? If yes kindly guide me.

[@deybieduardoochoaarias8330]

Thanks, I was looking for this example. It was a great help for me

[@ProlificOaktree]

Glad it helped!

[@weaq84]

Thank you for this. I already have data validation on the cell, to enable it to show a drop down list, but I'd like it only to be active if another cell has no data in it. It looks like I can't apply a second data validation rule to the cell. Does anybody know if this is possible?

[@MaryJeyaseeli-j2j]

☹

[@marc000]

Hello, Do you know if the users can modify the data validations, also the formating (conditional or not) etc... . Can we prevent this (or is it already) ? All what I know about this is : Protect cell => Unauthorized user cannot do anything on the cell. Unprotect cell => Everyone can do everything with the cells ... (format, validation etc ...) (afaik)

[@ProlificOaktree]

I don’t think you can protect that.

[@marc000]

Too bad. It would have been fine for my collaborative sheet. Unless (probably) writing scripts but then it' s much harder work ... Thanks for reply anyway.

[@jordanemery7069]

I want to lock cells in a row after entering the data in the cells in that row by selecting a yes or no from a drop down list at the end of that row. "is the data in this row complete?" yes = locked row. Can someone please help.

[@nyannyanmoto7963]

So you have a script for this? Doing the validation data is useless when user will paste a value to it as it will overwritten that particular cell.

[@ahmedshawkycrm480]

Thank you! Can I copy this and apply at once onto another Sheets with the same formats and design!

[@ProlificOaktree]

You should be able to.

[@superdeveloper5141]

Hello sir, is there a way I can make a Shared sheet on Google ( which will be a attendance sheet) where , Columns will be > Date | User 1 | User 2 :- 1) One user can not make entry in other user's column 2) no one can make entry/ edit in Past date 3) Hide Data Validation Option Under 'Data' Menu Option.

[@vpant]

Hello! I hope you are keeping well This is exactly what I was looking for but I do not have access to google sheets at my office. I have been trying to mimic your approach in Excel. Not able to get the result Please , suggest an alternative or may be an excel version of this. Thank you

[@ProlificOaktree]

Good idea. I'm going to look into it.

[@massimohouse438]

Hi There. Thank u for the great content. Is there a way to only allow cells to be edited on specifc dates? If i have a sheet where staff manually input their working hours that has the dates in column 1. Is there a way to stop anyone editing previous dates input values? Thank u

[@ProlificOaktree]

I would have to see the sheets, but I would think combining the IF and the TODAY function may help you out.

[@mohammadbadawi1400]

How to do if I want to lock based on the day date like enter data on the same day only

[@monikamachkovska2833]

Thank you for this. What is the formula if we have multiple values that satisfy the data validation? For example if we want to add tracking numbers for both Shipped and Pending?

[@ProlificOaktree]

Start with the AND function.

[@eashaantam6227]

hello sir, can I prevent change in one cell depend upon the date in another cell

[@ProlificOaktree]

Yeah. This video touches on that concept. kzbin.info/www/bejne/qnishWB_rNyArbs

[@GeoMacropulos]

If conditionally locked cells are built as formulas that rely on other sheets for the data presented, will those formulas continue to automatically work or will a conditional selection like "Closed" prevent the data from updating?

[@ProlificOaktree]

Hard to tell without seeing what you're doing, but I think they'd still work.

[@ruudje1976]

Thank you. Exactly what I needed, and well explained and demonstrated.

[@ProlificOaktree]

Glad it helped!

[@MaryJeyaseeli-j2j]

@@ProlificOaktree What if the validating range already contains dropdown criteria? how to add the data validation rule?

[@cityofclarksville599]

Is it possible to use conditional formatting to lock a cell after data input? For example, all cells will be blank and once a person adds text, it locks so others cannot edit it.

[@ProlificOaktree]

Not that I'm aware of.

[@samueldefelice8019]

Hey there just trying to get my question out in the open. I use google sheets everyday and I am trying to educate myself and use it to its full potential. You channel has a lot to offer can’t wait to dig in. But right now I am running into a road block. I’m not quite sure what the function is called. I have data on a google sheet. The data at the end is a list of vehicles that I have to work on. Some I get to and some I don’t. The ones I get to I set a drop down value with the word done and the row turns green and later I will delete the row. My question is how can when I select the word done get that vehicle to remove itself automatically off the master sheet and populate onto a secondary sheet storing the done values for later review? Thank you!

[@ProlificOaktree]

Getting it to deleted would probably be a complicated matter so not sure about that. To pull something onto another sheet based on a cell value, you could use a combination of an IF statement with a nested VLOOKUP inside of it.

[@samueldefelice8019]

@@ProlificOaktree Got it. thank you!!

[@tainaandrade443]

Thank you so much for the content!

[@alkaahmed32]

This was helpful.. do u have tutorial on one drive as well?

[@ProlificOaktree]

No, I don't. I've been using and probably should make one though!

[@sarbashisSarkar]

HOW TO CONDITIONALLY FORMAT A COLUMN VALUE BASED ON ANOTHER CELL VALUE THAT CHANGES IN REAL TIME LET SAY, FROM V3TO V17 I HAVE PE RATIO OF SOME COMPANIES. I AVERAGE THAT VALUE IN ANOTHER CELL (H7). NOW I WANT TO PUT A CONDITION IN V3 TO V17, WHICH CHANGES THE VALUE BASED ON VALUE ON H7

[@neatdesign78]

and of courseawesome tutorialsl nice intro music. ;) thanks

[@sajalkumarghosh6195]

Thank you Sir 🙂

[@JrgenEngen]

What if the cell you would like to lock is a tickbox. Rule needs to be "If tickbox A10=FALSE then you wont be able to check the tickbox in A11". Sounds simple enough. But whenever I try this solution the cell just automatically becomes a text-cell.

[@talibabbas6518]

Thanks for your efforts however, this is not working if users copy data from another file and paste it here.

[@lhctrekker]

it does work ,but mine comes with annoying little red triangle at the corner in every cell indicating validation error.How to fix it?

[@ProlificOaktree]

Paste the formula you used.

[@lhctrekker]

@@ProlificOaktree 【=$L2=""】 if column L is not empty ,the validated cell is locked, else allow input entry.

[@ProlificOaktree]

@@lhctrekker The only other thing I can think of is you should make sure you have Reject input selected and the Show validation help text box unchecked.

[@bachchuhaldar3783]

Sir,.. Please use apps script same function. (conditionally lock cell/row/ column based on other cell)..Thank you.

[@motherbarbelo7]

how do you skip certain cells that can not be entered and to cells that can be entered through tabbing. like doing data entry job.

[@ProlificOaktree]

Hold down your control key and press the arrows. See this video for more on this: kzbin.info/www/bejne/pp-qqYaed6x0r8U

[@motherbarbelo7]

@@ProlificOaktree thanks. what about if you want to skip cells that is protected and tab to the cells that can be modified. similiar to database style.

[@ProlificOaktree]

@@motherbarbelo7 I'm not aware of how to do that.

[@motherbarbelo7]

@@ProlificOaktree how do you skip a cell when tabbing in google sheets

[@RupinderDhamija]

Amazing.! 😁

[@regretnowdoitlater2936]

I tried this but they delete data from the protected cell or did i miss something?

[@bidrive3285]

Thanks a lot sir,

[@sayandey2529]

what if i want to validate cells for "Pending" and "Out of Stock" together?

[@ProlificOaktree]

Try using the AND function in your formula.

[@vishnutiwari7902]

How to lock cell if cell is blank using appscrit

[@Daniela-yh3sv]

Thank you so much :)

[@ronelarinal1865]

IF I DRAG OR DELETE IT STILL WORKS.. ONLY IF YOU INPUT WILL SHOW WARNING

[@sipirock9383]

not working if I using google finance :(

[@pichit.raetai]

Thank you

[@claystudiofx]

It has flaws : it won't work when you do the 'paste' into the cells (not typing direclty into the cells) or when changing the value of a check box etc. Make sure you 'type' the value directly into the cells to make this method works - do not doing copy/paste.

[@abdelhameedhamdy]

Thanks for the $ trick

[@hotmailuser503]

Hello, and God Bless. I tried to write the formulas like you did here and they did not work. Did it change when google updated?

[@ProlificOaktree]

No, the formulas work the same way.

[@praveenmuchandi7252]

what if shipping status is blank and we try to access the column were data validation is applied