Locking cells based on conditions Solved

Question

Hello,  I would like to perform an operation in Excel and I don't see how to do it other than through a macro. Unfortunately, my programming skills are limited, so I'm turning to you for help.  I have a calendar spanning several years broken down by week. I need to enter a date into a cell. Once the date from that cell is entered, I would like to ensure that all data to the left of the entered date cannot be modified (lock the cells) unless the user enters a predetermined code.  Is this possible?    If you could lend me a hand, that would be really nice.  Thank you in advance  Configuration: Windows 7 / Chrome 37.0.2062.124

Mike-31 · Answer

Hello,  It's entirely possible, even with a validation formula, but it would be more interesting to have a snippet of your file rather than a blurry screenshot so that we can work on it and suggest an appropriate formula. To attach your anonymized file, use this link  https://www.cjoint.com/  -- Catch you later Mike-31  A period of failure is a perfect time to sow the seeds of knowledge.

dédé65000 · Answer

Thank you for taking an interest in my case   you will find my file attached  https://www.cjoint.com/?3Jbrq5l1VR0

Mike-31 · Answer

Hello,  if I understand your request correctly, if you enter a week number in C3, for example 12, we need to lock the cells from B9 to M21 or simply each row B9:M9 similarly, if you enter 20 in C3, we need to lock the cells from B9 to U21 is that correct!  See you soon Mike-31  A period of failure is a perfect time to sow the seeds of knowledge.

dédé65000 · Answer

That's exactly it  you understood my problem well

Mike-31 · Answer

Re,  So select the range B9:DA21 Data/Data Validation/Option tab/in Allow, select Custom/in Formula enter this formula that will take into account the year and the week =AND(B$6=$C$2;B$7>$C$3)  you can also add a custom message in the dialog box, to do this before closing the wizard go to the Error Alert tab in Title enter a title and in Error message a short custom text  A+ Mike-31  A period of failure is a perfect time to sow the seeds of knowledge.

dédé65000 · Answer

Hello,  In principle, I understand well, but in practice, it doesn't work. Data validation is blocking all the fields for me.

Mike-31 · Answer

Re,  look at the attached file to see if it meets your expectations   https://www.cjoint.com/c/DJgkFSTNgub  -- A+ Mike-31  A period of failure is a perfect time to sow the seeds of knowledge.

dédé65000 · Answer

Yes, thank you very much, it's working. I must have made a mistake somewhere, so I will look for my error.  Thank you very much for your help.

Mike-31 · Answer

Re,  your request was relevant, I am marking the discussion as resolved so that it can serve as a reference, but nothing prevents you from revisiting it  -- See you, Mike-31  A period of failure is a perfect time to sow the seeds of knowledge.

Locking cells based on conditions

9 réponses

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french