Excel - Conditionally Lock Cells Based on Other Values

[@chester1234SVK]

Hello, thank you for the video - it worked for me with a bit more complex formula in the custom validation. Is it possible to make this a step further and when the cell is "unlocked", display a dropdown list of options?

[@nidiaymedinapaz2833]

Sir you are a life saver. It took some time and some trial and error but your video had everything I needed to know to make this feature work for me and this is going to save myself and my team a lot of time and a lot of stress.

[@ProlificOaktree]

Glad to hear it helped you out!

[@agungsatriawardhana1653]

Thank you sir. how about we conditionally lock based one condition, but in the cell we have 4 dropdown menu? for example we have C3 with 4 dropdown options: A, B, C, and D. And we want to lock cells C5, C6, C7 if only C3 is "D". So we only can input data in C5, C6 and C7 if the option in C3 is not "D". What is the formula for this case? Any reply would be very appreciated.

[@AllergicToFantasy]

Very helpful and easy to understand. Thank you.

[@ProlificOaktree]

You’re welks!

[@CynthiaLopez-e8m]

Hellooooo!!! Thank you for the tutorial. Very helpful. Question..... what if the "status" is not a drop down list but rather a vlookup. How can I still be able to "lock" the other cells?

[@ramanbalaji8891]

Saved my day! Thanks

[@roberth.9558]

Beautifully done! Thank you for this clear and informative solution to a problem which I can immediately implement.

[@mjseva]

Thanks for the video. On that 2nd tab, is it possible that instead of choosing the dropdown list first, you put in the date then the dropdown list will be enabled?

[@heidihansen7378]

Thank you. What if I would update the table with new item. and wanted keep the tracking number locked to that specific item. I I’m working on that at the moment, and can’t seem to find a way.

[@leonardostreit3527]

Great! Is there a way to lock a whole line to prevent changes after a condition like “finished”

[@mohamedmeshref]

Excellent video, I wish you can add Excel files to apply the video on it

[@ProlificOaktree]

Excel? What's Excel!

[@stefanogattoCH]

Nice! Thank you! Is it possible to have the same applied to cells that already have a Data Validation set, such as a curreny dropdown (List)?

[@ProlificOaktree]

I doubt it.

[@stefanogattoCH]

@@ProlificOaktree ok thanks! Then i use VBA

[@timothyibrahim2591]

Quite helpful. Thank you

[@ProlificOaktree]

You're welcome!

[@monicareyes9817]

I need help. I want to lock columns that can only be opened with a specific value. for example. Column B to M is locked then if i put "1" in the Cell of Column A, the corresponding row of columns B & C will be unlocked. then if i put "2" the corresponding row of columns B,C,D & E will be unlocked. I need formulas :(

[@GuruprasadGPalan]

It works partially. We cannot edit data but we can paste any value we need.

[@ratchaponsittikaew5272]

Thanks so much for sharing this tip sir.

[@paultimothydelosreyes4494]

At long last, after days of formula hunting :) Thank a lot for this Sir :)

[@ProlificOaktree]

I'm glad you found it!

[@jackybajenting]

hello , thanks for this. Question : How to prevent copy paste locked cell. although you can locked but pasting on the cell is still possible'

[@ProlificOaktree]

Not that I'm aware of.

[@DeborahAbelha]

Thank you very much!

[@mommypowerrr]

hello good sir, so i want to lock some columns based on another column. let's say i have this INSURANCE drop down column that has three options and if this column is left blank, the 3 other columns (e.g. type of insurance, etc) need to be inactive (locked). but when that column is filled, well the 3 other columns need to be active. can you please help me with data validation formula? cause i've tried a few to no avail :') thank youu

[@baqaraligora4299]

i also want same

[@naseeryounasbhatti1077]

Sir, its Very Nice Trick to lock cell. thanks for it. Sir, my question is how to disable delete key to prevent this formula in future. if some one use delete key on these validated cell these validations will be deleted. how it is possible without password protected sheet.? or cells

[@ProlificOaktree]

I'm putting this question in the que to answer tomorrow during the live stream at 11 am. Hope to see you there. Look at my channel page for the link.

[@naseeryounasbhatti1077]

Thanks u sir for u r rply can u send MN the link to join live session.?

[@s.m.shahedhossen8488]

Great. Thanks. Jazakumullah.

[@RishabGoel1994]

hi what if our criteria is two or three words like column e and f accepts values if coumn d has written shipped and complete words in it?

[@ProlificOaktree]

Take a look here. You might want to use this function sheetshelp.com/countifs/

[@jacheto]

and how do I prevent someone from erasing the existing value?

[@moukthikareddy_08]

thank you🙂 for the video

[@ProlificOaktree]

You're welcome 😊

[@Saady2s]

How about if Column A1 has any value, Column B1 should not allow any value to be entered. How to enforce this?

[@johnboffin476]

Hello Sir, I want to do something similar, Actually I tried it and I failed. Let me explain. I am creating a balance sheet with date column, Codes column with data validation drop down list, details column with drop down list, general expenses column, income column and balance column. I want to use validation custom selection in general expenses, income column and lock each column unless codes column drop down list for instance if selected Sales it allows income column but not general expenses, In general expenses I have 39 items in list and in income I have 12 items. I can lock the balance column so it's always safe. balance does the calculation (no entry required in balance column) I only want to safe guard that data doesn't get entered in wrong column. (Six columns and 1200 rows) Please help if you can. thanks

[@nice6585]

Thank you for the video. I have question though. How about if Column E already has a data validation list. My file is Column A - list where user can choose product Column B - word approved appears if manager approves the product move What i need is that, when it is already approved, Column A should be locked and no longer be edited. I tried doing it as Data Validation formula on Col A but it will overwrite my list. Can you help? Thanks!

[@dustinhoang7183]

Much appreciated!

[@cameronhampton1315]

Thank you!

[@leutrimjashari5011]

Hi, Understand that this is based on one criteria, how about if you need to add more than one, for example Shipping and Pending?

[@ProlificOaktree]

Look into using the OR function.

[@vikrantgupta8749]

Is there any way that after applying that data validation formula, we can get data from a specified list instead of manually typing

[@ProlificOaktree]

There's a "list from a range" option available in the data validation. Maybe that would help.

[@A1994y1225N]

But, when there are some other Data validation set, it replace all previous. Can't be use when you need some other settings o data validation setting for some other cell or columns.

[@A1994y1225N]

How could i block the all line from A10:AA10, based on on cell content. For example cell X10="Closed". ? Best Regards.

[@gjwilliams4098]

Users can still paste values. Any solutions?

[@abdulhamidalhaddadi2255]

THANK YOU SIR

[@ProlificOaktree]

Most welcome

[@Neverdie4916]

Perfect!

[@vigneshperumalsamy6759]

Thank you

[@CletoClips]

Is there a way to apply conditional formatting (Excel or Google sheets) to a list of words at one time, instead of one value at a time? Like, highlight ALL these pronouns instead of individually formatting each one.

[@NexGil]

Thank yoi!

[@KoryGraphic]

But how to apply this rule to every row and its corresponding row on column D? This example is quite useless as it makes all rows rely on row 3... who would use that?

[@ProlificOaktree]

I'm not sure what "every row and its corresponding row" means.

[@Tat-cx1lr]

Merci

[@ProlificOaktree]

You're welcome!

[@iiiiii-w8h]

No. You are not locking cells. You can easily copy and paste a tracking number anyways.

[@BusinessBananaShikhe]

Sir,,but data is delating how can restricted

[@mouhanadalazem8339]

thank you

[@ProlificOaktree]

You're welcome

[@Batman-sb3tn]

Thank you