• 513K Members
• 2,763 Online
• 611K Conversations
SOLVED

Can I delete a cell entry, but lock a cell formula so it's retained?

Occasional Contributor

Can I delete a cell entry, but lock a cell formula so it's retained?

Hi

I use an Excel work sheet as a yearly accounts ledger / accounts book. The worksheet has 12 tables, each table being a month of the year. Column headings in each table include 'Amount ex VAT', 'VAT' and 'Total including VAT' (VAT = Value Added Tax). Each row in each table is for items of expenditure. Each of the aforementioned columns will contain the amounts for each row, and each column will have a total at the bottom of each column. The idea is to enter an amount in a cell for an 'amount ex VAT' row/column, then in the 'VAT' column, a formula will multiply that amount in the 'amount ex VAT' by 20% and show the product in the VAT cell, then in the cell in the 'total including VAT' row/column a formula will add together the amounts in the 'amount ex VAT' and 'VAT' and represent that total amount in the cell. I've inputted all the formulas in the relevant cells and it all works perfectly.

However, the problem is that if I enter an amount in a cell which contains a formula, and then need to change the amount entered, I can accidentally delete the amount entered, and delete the formula as well.

Therefore, is there a way that I can lock the formulas in their cells so they cannot be changed/deleted when deleting a number/value in a cell, so be able to delete a cell entry (e.g. an entry of £20) without deleting the cell formula at the same time?

Thanks

7 Replies

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi Reboot, so just to clarify you want to add an extra amount into a cell that contains a formula and then change that amount but avoid deleting the formula part of cell?

If that's the case my initial suggestion would be to add a reference to an input cell in your formula and then you never need to touch that formula again. The input cell would then have the entry of 20 or 0 etc.

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi Mr Hopkins

I'm what you might call a basic user of Excel, so only have limited knowledge of its functionality. What is a reference cell please, and how would I set it up in the way you refer to?

Thanks

R

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi

It would help if you could attach an example but essentially if your formula is =A1 x 10%  and you want to add 20 then rather than adding the 20 to the formula   = A1 x 10 +20   you should put the 20 in cell B1 and the formula would be  A1 x 10 + B1

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi

Please find attached an example (I hope I've managed to attach it OK).

I'm not sure but I think we've been at cross purposes. You can see in the sheet that there are formulas in the 'VAT', 'Total Incl VAT' and 'Totals' columns/rows. If I delete an entry in the 'Amount Ex VAT' column, there's not a problem because the cells in that column contain no formulas. However, if I delete an entry in cells containing formulas, both the entry (e.g. £20 in the VAT column) and the formula are deleted; this is a problem because when busy, it's easy to accidentally delete the entry and formula when just the entry was meant to be deleted. Therefore, can I lock (for want of a better word) cells with formulas so that the entry can be deleted while leaving the cell formula untouched/in place?

Thanks

R

Solution

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi

Thanks for the attachment

You can lock cells from accidental deletion.

The technique is to actually unlock the cells you want to type in and then protect the worksheet

Highlight the Amount cells and right click and choose Format Cells, then go to the Protection tab and uncheck the Locked box

Then go to Review > Protect Sheet and you will only be able to edit the "unlocked" cells

Highlighted

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi

Thanks for the attachment

You can lock cells from accidental deletion.

The technique is to actually unlock the cells you want to type in and then protect the worksheet

Highlight the Amount cells and right click and choose Format Cells, then go to the Protection tab and uncheck the Locked box

Then go to Review > Protect Sheet and you will only be able to edit the "unlocked" cells

Re: Can I delete a cell entry, but lock a cell formula so it's retained?

Hi

That's done the trick. Thank you for your help, much appreciated.

Cheers

R

Related Conversations
delete team as last member not an owner
Alexis Dailey in Microsoft Teams on
1 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies