cell protection;auto insert row with formula and overtime computation

[gerard_gonzales33]

Hi everyone!

Greetings of good health!

My problem as follows:

1. How can I or is it possible to protect a cell(s) without entering a password? so the user can use the particular cell (colored dark blue)?

2.Is there a way that a user can auto insert/add row and copy the formula in a click of a button?

3. In computing the overtime hours what will be the formula if the conditions are: a. in weekdays (every Mondays to Fridays F10;G10) the start of overtime will be 6:00PM; b. in weekends (Saturdays and Sundays F12;G12) the lunch break (12:00pm to 1:00PM) is not computed in the overtime hours.

4. How to come up with the result in 0:00 if there is no time entry were encoded?(N11,N14 to name a few)

Attached is my sample file.

Thank you in advance.

[dilipandey]

Hi Gerard,

Below are the answers:-

1. Explore the option -> Review -> Allow user to edit ranges
2. Yes.. it would be simple one.. Insert row and Ctrl + d (Same can be triggered using macro, which can be written or recorded)
3. Check the attachment, yellow cell
4. Check the attachment, yellow cell

Attached is solved sample file.

regards,
DILIPandey

<click on below 'star' if this helps>

[gerard_gonzales33]

Hi DILIPandey;

Thank you very much in assisting me in my concerns. But I have another concern regarding how to use the macro, I dont know how it works, where it is located and biggest problem is how to use. Hope you could help with this part.

Thanks in advance.

Gerard

[dilipandey]

Hi Gerard,

Macro can be recorded to cover below point:-

2. Yes.. it would be simple one.. Insert row and Ctrl + d (Same can be triggered using macro, which can be written or recorded)

Go to Developer Tab, click on Record Macro... now select any row above which there is an formula row (suppose row 9 has formulas, select row 10) and press ctrl +D.. stop macro. press Alt + F11 you will see module 1 on the right window, double click on it and copy the code form left window and share with me..

I'll make you learn how to record a quick macro and customize that further. Happy learning. thanks

Regards
DILIPandey


<click on below 'star' if this helps>

[gerard_gonzales33]

Hi DILIPandey

Am having problem from the start (am sorry). I followed your instructions, I select row 11 but I didn't see the module 1. It just open another window-the "microsoft Visual basic". I hope you have patience with me. I really really want to learn this macro.

Thank you so much in advance.

[dilipandey]

Hi gerard,

You selected row 11.. did you checked that row 10 ( above to selected row) has the formulas?

Go to Developer Tab, click on Record Macro... now select any row above which there is an formula row (suppose row 9 has formulas, select row 10) and press ctrl +D.. stop macro. press Alt + F11 you will see module 1 on the right window, double click on it and copy the code form left window and share with me..

Did you followed above ?

share your learning and code. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>