By thurmbo ·
Hi, I am trying to protect specific cells that contain formulas, but also need to allow others to cut/paste rows (that have protected cells) to other sections of the sheet.

I have tried to protect the sheet using all the of checkbox configurations, but can't seem to find a way to allow cut/paste rows that contain individually protected cells.

Does anyone know a way to do this? Much appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

I don't understand

by LocoLobo In reply to Excel

Do you want to move the protected cells to another location?

Would Copy -> Paste work? Copy will work and in Excel 2003 you can allow inserting rows and columns. Nothing I tried worked either. Which is good for me. We don't want our users moving protected cells around.

Maybe someone else here can help :)

Collapse -


by thurmbo In reply to I don't understand

Yes, I have a need for my clients to be able to copy/paste additional Rows (of a templated Row) so they can log in specific data. However, there are locked cells within that Row to prevent them from altering Formulas. So they are unable to Copy & Paste unless I keep the sheet unprotected, which defeats my purpose.

Thank you,

Collapse -


by LocoLobo In reply to Clarification

So far Toms45 has the best idea. The problem with that is your cells referencing the formulas can't be protected. Or at least that's my understanding.

In my trials yesterday with Excel 2003 you can allow "Insert Rows / Insert Columns", but it had to be the whole row/column. I was unable to "Insert copied cells". Maybe MS will have that available in Office 20XX?

Good Luck

Collapse -

Thank you

by thurmbo In reply to OK

Unfortunately I am unable to keep cells locked (regardless if a formula resides in the cell) in fear that clients will type in the wrong cells. I realized I still have cells that need to be locked so the tab key will skip over them.

I thank you for your trials & troubleshooting in regards to my dilemna!

Much appreciated

Collapse -

Named range may work

by toms45 In reply to Excel

Try putting your formulas way down in row 3000 column az in a named range, then reference the named range in the calculated cells. Seems like I remember something like this from about 10 years ago.

Collapse -


by thurmbo In reply to Named range may work

Thank you, I still could not get it to work properly as I had hoped to, but your suggestion taught me something new that I can apply to other areas of my spreadsheet.

Much appreciated!

Collapse -

Did you ever get this to work?

by liz In reply to Toms45

I have a similar problem, and wondered if you ever got your problem solved?

Collapse -

Still working on solution

by thurmbo In reply to Did you ever get this to ...

Hi Liz, I have not been able to get this issue resolved, but will be looking at fwang's helpful hints.

Thank you fwang!

Collapse -

Fwang's advice helped in a solution

by liz In reply to Still working on solution

As he very simply suggested, I "wrote" the macro to include the unlocking of cells prior to adding rows, then locked cells to finish off the macro. It worked beautifully.

I still want to fine tune the macro so that it is able to properly add a row and replicate formulas for one row after another. I will us the Goto and Offset function for that.

Collapse -

There are two ways to do this

by fwang In reply to Excel

1) Give user password (or protect sheet without password), unprotect sheet, then do cut/paste, after that, protect sheet again.

2) Do not put any formula, after user finishes all the cut/paste job. Run a macro to generate all the formula. You can also use macro to protect the final sheet and lock it.

Related Discussions

Related Forums