Inserting a row with a formula into a protected worksheet


New Member
Jun 1, 2017
Good Evening Excelperts

I like to think I am an intermediate excel user, but like everyone I know really well the functions that I utilize daily and I know that I have lots to learn!

So, first if anyone knows of a great learning resource I would happily check them out! *Keep in mind that I am situated in Mid Alberta CA

Second...Here is my head scratcher:

I have an excel sheet that lists residents of a building in alphabetical order. There are columns to enter the rent/laundry/communications and so on leading up to a column that sums up the row. The person that is manning this spreadsheet is a novice excel user so I want to protect the spreadsheet so that the formula can not be over written. The user needs to be able to insert a row where needed in the spreadsheet. This I can do already.

What is tripping me up is to have the formula auto fill in as well. The formula is just a simple sum (example: =SUM(E5:R5)

So ultimately I would like to be able to inert the row where needed and have the appropriate formula populate (if I insert a row and it is 20 the formula should read =SUM(E20:R20) or is it is row 26 it would be =SUM(E26:R26) and so on) All the while keeping the sheet protected.

I have tried a different approach which was to create a template row that has everything that I need (formula and colour of row that we use for new residents) While protected I thought that I could just copy the row and "Insert copied cells" but all I get is a message "The cell or chart you're trying to change is on a protected sheet. To make changes, click unprotect sheet in the review tab, you might need a password"

Please let me know if you need further information in order to help me.

I have seen others with the same challenge, but I am VERY new to the Macros/VBA I find it confusing as how to make what is offered work for my spreadsheet.

Thank you in advance Everyone! and I hope one day to pay it forward.


Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics