Forum Discussion
Adding a formula to a column and make it auto-repetitive and deactivate the column
To do that you should highlight the cells/columns that the user is allowed to edit, right click and go to format cells, and under protect uncheck 'Locked' so those cells are NOT locked and then under the Review tab select Protect Sheet and you can select from various option and choose to give a password or not. This will prevent the user from editing that column or any other columns that you left 'Locked'.
As for automatically expanding the formula into additional cells I addressed above.
I recommend the 1st option with making a table. Highlight the area with the data and go to the home tab and select 'Format as Table' under 'Styles'. The make sure the range is correct if the first row is headers or not. Then when it is formatted as a table, when the user starts a new row, Excel will automatically expand the table and copy any formula accordingly.
mtarler I am making a checkbook that need to continue the balance forward. Is there a clear instruction for making a column a table? I am so tense right now because I am afraid of making a mess of things. Right now I have to manually add the formula and it is exhausting.
thanks for your help
- Hi Pegi C, First off you probably should start a new thread with this question (unless my answer is all you need).
As for you question, when you do post in a new post please try to include more information about exactly what you want or need and hopefully attach an example (no confidential/personal info) or give a link to a shared workbook in sharepoint or onedrive or using something like dropbox.
You asked about "making a column a table" and above I noted how to make a table:
"Highlight the area with the data and go to the home tab and select 'Format as Table' under 'Styles'. Then make sure the range is correct and check if the first row is header row or not. "
As for making a running total you if we assume the AMOUNT is in A2 and then you want the RUNNING TOTAL in column B then in B2 you type =SUM(B1,A2)
When formatted as a table that column will autopopulate and fill down as you add new rows.mtarler : Sorry this took me so long to get back to. I don't know how to look for what I previously asked.
I am working with a checkbook type of situation. I am not sure what "deactivate the column" means. This is what I need.
The formula for the balance of checkbook at the current moment has to be added after each transaction. This is a time waster and I know that there are solutions for this. I need it to automatically add or subtract to or from the last balance and show the new balance. I just want the formula to repeat. My problem is that when I add a new transaction, the previous balance must be addressed in the formula and then the current row + or - from that to get new balance. I hope that I've made myself clearer than the first time I requested help.
This is the formula I use, "H" is the balance column [ F is the expense, G is the income]
=H-F(current row)+G(current row)
I hope this is understandable. Thanks again for your assistance.
