winterjade
New Member
- Joined
- Jun 1, 2017
- Messages
- 5
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 coding...so 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.
Winter
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 coding...so 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.
Winter
