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

[Reboot]

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 

[#2]

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.

[#3]

Hi Mr Hopkins

Thank you for your reply.

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

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

[#7]

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

[#8]

Hi

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

Cheers

R

[#9]

I followed these steps however the unlocked cells when edited and a user clicks delete the formula deletes. How can I protect the formula from a user deleting it on accident when they're trying to only delete their work? @Wyn Hopkins 

The photo below shows where the green highlight is at, the formula that was present is deleted and where the $ signs are the formula is still present.