Need to insert rows with formulas in Protected Sheet

[jshaffer]

I am very much self taught when it comes to most things in Excel so I am not even sure if what I am trying to do is possible. I have a worksheet that is being used to track information from the production runs. I have the sheet locked and protected except for the cells I want them to be able to type in as I do not want them to adjust the formulas that are in the sheet. I also need them to be able to insert a new row in they have more than one work order that ran on that machine. I need the formulas to auto populate once a new row is inserted. I have a couple issues I am running into. First even though I have the insert row selected while the sheet is protected it isn't letting me insert any rows. Also I am not sure how to go about having the formulas auto populate once the row has been inserted. I am not sure if a Macro is the correct way to go about this. If so I am not very skilled in them or if what I am wanting to accomplished just isn't possible. If you could let me know either way that would be great. Thank you for the help!

 

[DataBlake]

I am not even sure if what I am trying to do is possible.

first of all everything is possible in excel
secondly i think creating a VBA code to unprotect the worksheet, do the specific changes you need from the user, and then re protect the sheet would be the best course of action for you.
that way enduser won't mess anything up thats not intended to be changed.

if you supply an example of your sheet via screenshot, direct google doc download, or using mr.excel's table maker.
as well as an example of the end result

anyone would be happy to assist you further.

 

[jshaffer]

first of all everything is possible in excel
secondly i think creating a VBA code to unprotect the worksheet, do the specific changes you need from the user, and then re protect the sheet would be the best course of action for you.
that way enduser won't mess anything up thats not intended to be changed.

if you supply an example of your sheet via screenshot, direct google doc download, or using mr.excel's table maker.
as well as an example of the end result

anyone would be happy to assist you further.

Below I used the table maker to show the sheet and current formulas. The empty cells are the only cells that would be unlocked. Everything else would be locked. I am looking to add a row in between the machines as needed for when additional work orders are produced. Thank you so much for the quick response and let me know if you need me to show you anything else.



[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Machine[/TD]
[TD]Product Number[/TD]
[TD]Planned Piece Made[/TD]
[TD]Pieces Made[/TD]
[TD]Downtime Min[/TD]
[TD]Cycle Time[/TD]
[TD]Efficiency[/TD]
[TD]Reason for efficiency no notes needed when above 85[/TD]
[/TR]
[TR]
[TD]MM3[/TD]
[TD][/TD]
[TD]=VLOOKUP(B4,'cycle info'!A2:E5075,5,FALSE)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IFERROR(F4/E4,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MM5[/TD]
[TD][/TD]
[TD]=VLOOKUP(B5,'cycle info'!A2:E507,5,FALSE)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IFERROR(F5/E5,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MM6[/TD]
[TD][/TD]
[TD]=VLOOKUP(B6,'cycle info'!A2:E509,5,FALSE)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IFERROR(F6/E6,0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DataBlake]

nevermind, after seeing the table maybe formatting the columns out of protection would be best
this article will be able to explain how to do that better than i will.

let me know if that does not solve your issue