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
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.
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
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
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?
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?
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.
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.
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!
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.
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
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.
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.
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)
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.
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.
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?
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
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.
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
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?
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?
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!
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.
@@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.
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.
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.
@@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.
Did you know that people like you, are great. Thank you very much.
/Seems like this does not work if the cell you are supposed to lock is a drop down list :( is there any alternative?
same doubt...did u get a solution?
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
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.
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
Thank you. Exactly what I needed, and well explained and demonstrated.
Glad it helped!
@@ProlificOaktree What if the validating range already contains dropdown criteria? how to add the data validation rule?
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
same as mine
Thanks.. thats what i looking for some times.. thank you so much..
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?
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?
Thanks, I was looking for this example. It was a great help for me
Glad it helped!
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
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.
looking for this too❤❤❤❤
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.
Thank you so much for the content!
Thank you. That's exactly what I looking for, but I have one question: How can I not ignore the blanks in the conditionals?
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!
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.
Thank you Sir 🙂
what if shipping status is blank and we try to access the column were data validation is applied
I tried this but they delete data from the protected cell or did i miss something?
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
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.
and of courseawesome tutorialsl nice intro music. ;) thanks
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.
Thanks a lot sir,
How to do if I want to lock based on the day date like enter data on the same day only
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.
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)
I don’t think you can protect that.
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.
Thanks for your efforts however, this is not working if users copy data from another file and paste it here.
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.
Not that I can think of.
@@ProlificOaktree sad
Amazing.! 😁
hello sir, can I prevent change in one cell depend upon the date in another cell
Yeah. This video touches on that concept. ruclips.net/video/tGIT-IwwMwY/видео.html
Thank you
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?
☹
Thank you so much :)
what if i want to validate cells for "Pending" and "Out of Stock" together?
Try using the AND function in your formula.
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
I would have to see the sheets, but I would think combining the IF and the TODAY function may help you out.
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.
Not that I'm aware of.
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
Good idea. I'm going to look into it.
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?
Hard to tell without seeing what you're doing, but I think they'd still work.
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?
Start with the AND function.
Thanks for the $ trick
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!
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.
@@ProlificOaktree Got it. thank you!!
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.
Hold down your control key and press the arrows. See this video for more on this: ruclips.net/video/pnGxShBGAyc/видео.html
@@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.
@@motherbarbelo7 I'm not aware of how to do that.
@@ProlificOaktree how do you skip a cell when tabbing in google sheets
Thank you!
Can I copy this and apply at once onto another Sheets with the same formats and design!
You should be able to.
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.
Sir,.. Please use apps script same function. (conditionally lock cell/row/ column based on other cell)..Thank you.
This was helpful.. do u have tutorial on one drive as well?
No, I don't. I've been using and probably should make one though!
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.
How to lock cell if cell is blank using appscrit
not working if I using google finance :(
IF I DRAG OR DELETE IT STILL WORKS.. ONLY IF YOU INPUT WILL SHOW WARNING
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?
No, the formulas work the same way.
it does work ,but mine comes with annoying little red triangle at the corner in every cell indicating validation error.How to fix it?
Paste the formula you used.
@@ProlificOaktree 【=$L2=""】 if column L is not empty ,the validated cell is locked, else allow input entry.
@@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.