Forum Discussion

Reboot's avatar
Reboot
Copper Contributor
Jan 07, 2019

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',...
excel
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Jan 08, 2019

    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

     

     

     

     

Wyn Hopkins's avatar
Wyn Hopkins
MVP
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.
Reboot's avatar
Reboot
Copper Contributor
Jan 08, 2019

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

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jan 08, 2019

    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

     

     

    • Reboot's avatar
      Reboot
      Copper Contributor
      Jan 08, 2019

      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

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Jan 08, 2019

        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

         

         

         

         

Resources