Insert Rows (randomly), Copy/Paste above's format & formulas, while PROTECTING formulas

I have a spreadsheet (multiple documents use same format) that contains formulas in multiple columns (column D, F, G, H, I, etc). These formulas need to be protected from being overwritten/deleted/etc. (unless user has a password).

The issue I have is I need our numerous users to be able to randomly add rows throughout the spreadsheet so they can add new data (ie: insert a new row at 23, 39, 55, 71, 89, 105, etc. - there isn't a pattern), copy the format and formula from the row above insert (or below if that is easier) while protecting the formulas in the spreadsheet.

So basically is there a way to protected formulas but allow a user to copy and insert random rows throughout spreadsheet, while continuing to protect the formulas?

Hi Shannon,

Not sure what you're trying to achieve, but have you had a look at the options available when you protect the sheet?

You can see a list under Allow all users of this worksheet to:
Some of the options are Format Cells and Insert Rows

Try ticking those options,

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David

I think I am needing a VBA code that will copy the row above when a user inserts a new row [randomly throughout document]. But also protect the formulas in the rows from being edited [located in random cells in the spreadsheet].

I just don't know exactly what I need.

I have tried protecting the cells, but it won't let users copy / paste the row above insert. I am open to letting users manually insert rows [randomly] and then copying and pasting the row above, but the protection won't allow that. I am not sure what I am doing wrong.

Originally Posted by FDibbins

Why cant they just add the data to the end of the existing list? You could then structure the formulas so they show nothing until something is entered into a specofic cell (date?)

I sure wish it was this easy. The spreadsheet contains multiple entities that are tested and the results have to be entered under each entity with the date and results in the inserted row.
If there is a way to create a condition that will insert the formulas when the user enters the date in col A, row [insert], then I am all ears, but I am not that fluent in excel.

ie: user inserts row 23, in cell D23 formula "=c23-b23", in F23 "=d23*e23", in G23 "=$c$8-b23", etc. so I am at an 'stump'ing point on this. and goes without saying that the row no. would change with each inserted row.

I am not familiar with Data Validation, but going to review and see if I can utilize the feature

PROTECT FORMULA - 2042 WL TABLE.xls

Here is one of the tables in question.

So a user is going to enter the data we will receive for our December 2015 sampling event. The user will insert a row under the June 2015 data. They should manually enter data in column A, B, C, E with the remaining cells (D,F,G,H,I) having formulas that need to be protected from user manually typing over them.

I hope this helps.