I want to prevent access to cells in a column until another value appears in another column?

[hotkya]

I have data being copied and pasted to column A then it will automatically put a timestamp (START) to column B. After an employee is done checking the data, he/she will select a status in column C the column D will have another timestamp (the END time). Column E will show the duration, or minutes the employee took to finish checking the data.

I need cells A2:A250 to be locked or protected until after the duration is showing in column E. The employee can only paste another data in A2 after the data in A1 was completely processed (where Start, End and Duration timestamp is present).

I hope someone here can help me with the formula or conditional formatting maybe…to do this. As much as possible not macro because I’m not really familiar with macro.

[anon73440385]

Some parts regarding “it will automatically put a timestamp (START) to column B” of the workflow / question is unclear. How will you manage this or is it part of question (Same of course applies to the END timestamp) how to achieve this?

[hotkya]

That’s not part of the question. I got that working already. I’m only looking for help on how to lock cells A2:A250 until after the first row A1 is filled with start time, end time and duration. Same goes after A2 then so forth…

[anon73440385]

Hello

to achieve “conditional unprotecting” perform the following

• Set all cell’s besides A2:A250 to unprotected ( Untick Format -> Cells... -> Tab: Cell Protection -> Option: [ ] Protected

• Define a cell style “Unprotected” ( Styles -> Manage Style, Click leftmost icon cell styles and Right Click -> New on Default)

• Select range A2:A250

• Define a Conditional Format: Format -> Conditional -> Condition using

  Formula AND(E1>0;E1<1440) (just an example for a possible condition minutes must be greater than 0 but less than 1440 [=1 day])

  Apply Style Unprotected (the style defined above)

• Protect your Sheet Tools -> Protect Sheet (if you don’t provide a password, every user of the sheet may unprotect the sheet). This step is important otherwise cell protection format has no effect at all.

See also the following file ( with "Protected Sheet* without a password, so you could modify or play around with):
ConditionaFormattingUnprotect.ods

Hope that illustrates the idea at least to conditionally unprotect - but see also my comment, since I don’t have any idea how you’d like to automatically add any timestamps in column B and D.

[hotkya]

Hi Opaque!

Thank you for helping me on this. I checked your attachment and that is exactly what I need. However, I cannot replicate it to my file. I followed your instructions but it didn’t work. I created the Unprotected cell style and followed your conditional formatting. I noticed that under the Cell Style Organizer Tab the “Contains” part is empty. Unlike yours it seems that there are conditions. Please check my file from this link and I will appreciate if you could help me create the cell style and conditional formatting:

My Daily Productivity Tracker.ods

The file is currently unprotected so you could check my formulas and conditions.
My objective here is that an employee cannot fill out A5:A250 unless A4:E4 is filled. I need A4:E4 to be protected so it can’t be typed on with numbers to activate cells A5:A250.

Thank you so much in advance

[anon73440385]

I need A4:E4 to be protected

I don’t understand that. How should that work, if data to these cells is the condition to unprotect other cells in next row. Now your requirements (which I don’t understand now) seem to be different than those from your original question, which has the condition ala Allow input to next row. only if a row has a result (column E)

Also downloaded your file, but it doesn’t work form me at all. Can’t see any "Unprotected* cell style, cannot modify Default cell style, cannot watch cell style Normal 2 16 (seem to have blanks, which i consider a bad idea), didn’t find any conditional formatting related to my answer. So can’t comment anything on that file.