Log In
Register
Home
September 16, 2016
Offline
Hi.
I have a bookkeeping workbook I'll be sharing with 87 financial agents to enter their bank statements into. Some may need only 10 rows, some may need 200. I'm supplying the workbook with just 16 rows so it doesn't appear overwhelming, but many will need to add additional rows.
I have locked several of the cells and a few columns that have formulas. I'd like the users to be able to copy an entire row, then insert copied cells into a new row, should they require additional rows for their data. When protecting the sheet, I checked the insert rows box, but it seems that because some of the cells within a row are locked, I'm unable to copy the row when the sheet is protected.
Is it possible to keep certain cells locked, but allow users to insert new rows, and copy and paste formulas from existing rows while the workbook remains protected?
I've attached the workbook. The Ledger sheet is the one in question. Columns H & I need to remain locked, as do rows 19, and 22-26. But I need additional rows to be added/inserted at the bottom after row 18.
Thanks in advance for any help you can offer!
P.S. Please don't judge me, I didn't create this file (its a bit of a mess)... I'm just the one stuck fixing it!
See Answer
July 16, 2010
Offline
Hi Sari,
No, sorry, you can't paste into the newly inserted rows because they're inheriting the protection from the row above and columns H & I are protected.
Mynda
Answers Post
Trusted Members
December 20, 2019
Offline
As Mynda has confirmed that it cant be done with usual protection etc, so how about a Macro work around? Just click on the button to add more rows (only does 1 at the moment)
A couple of things to note (not judging!)
- Merges Cells are evil and will only cause problems ( i have unmerged the subtotals part to make the macro work). i would get rid of all merged cells and, if needed replace with center across selection
- I have hidden a row just above the subtotals row - this is a work around to make sure the sum()'s extend the range as we insert a new row
- You need to check out some of the formulas as i noticed that at least 1 doesnt reference the cell one above in column H
Anyway, see if it does any good
The following users say thank you to Purfleet for this useful post:
Mynda Treacy
1 Guest(s)
