how to allow inserting rows in table while restricting user from editing cells with formulas

how to allow inserting rows in table while restricting user from editing cells with formulas

for example i have a table where i want to protect cells that has formula in them, and then i want also user to be able to insert new rows.
problem is that when i lock cells with formula, it also grays out the insert option.

any help is appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher Jay WolffWiggle My Legs, OwnerCommented:

right-click cell, select protection tab, check the box for Lock cell, then go back to sheet and on main ribbon click the Review tab, then under the category of Changes, click on Protect Sheet, then you'll see you options in the Protect Sheet dialogue box for what you want to allow users to be able to do.
FloraAuthor Commented:
please see attached workbook.

column M2 to M7 are the only cells that are locked and has formulas.

now when i protected sheets, i also selected allow insert row and you can see pretty much all options are  tick marked.  but still when i try to insert the row, it gives me warning and also when the new rows are inserted my formula in table which should automatically populate to any newly created row, it does not .

any idea how to overcome this problem?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook and click on the button Insert New Row, that will insert a new row in the table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher Jay WolffWiggle My Legs, OwnerCommented:
Hi people.
Interesting.  Sorry I didn't know they had a bug or design issue with the Protect Sheet menu options or checkboxes or tics.  So you'll need Sktneer's VBA or some other work around.  If you would like to provide more requirements maybe a work around could be keeping the totals column on a different sheet, then lock THAT sheet and when the user inserts rows it doesn't mess with locked and protected cells sheet.  If that is what you want let us know.

It appears everything works with the correct sequential formula being populated when inserting a row, only when sheet is not protected.

When cells in M are locked and the sheet is protected, as you also did, I checked the boxes for allow insert rows and delete rows, and selecting locked and unlocked cells.  After inserting a row and clicking through warning, the formula does not populate as it should.  I also cannot delete the row as described in the Excel help attached here.  If you look at the cells in column M after inserting this row, they are all locked still including the newly inserted total cell.  So that is probably the design issue they need to work around.  Might take an additional dialogue box for the user to get through the insert row process.  Anyway, I cannot see any way to make it work with the table you have here.  If given the features to lock cells and protect sheet with password, and still allow some user interaction based on those checkboxes, it should have been completed with all permutations of the menu, I'd think.  Of course thinking can be difficult.
FloraAuthor Commented:
many thanks to both of you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.