Excel: remove cell content but retain formula

  • Thread starter Thread starter Cliver6
  • Start date Start date
C

Cliver6

-- I want to create a workbook to use as a form for ten novice users. This
work book will have cells that contain formulas that cause those cells to
show data from other cells.

I need to be able to delete the data from the cells without deleting the
formulas. Then the workbook can be reused in its original condition without
having to rewrite all the formulas.

Cliver6
 
Edit | GoTo | Special | check the Constants box | OK
Now all cell with data other than formulas will be selected
Tap the Delete key and they are gone

This could be recorded and made into a subroutine
best wishes
 
So you want to be able to overwirte the formulas and then get them back
again? No can do. You could create a template allowing users to open a fresh
copy each time.
 
I have a similar problem. In my case, I have a large spreadsheet. Each line contains cells detailing sales transactions. some of these cells contain formulas. Typically cells in the same column will contain similar formulas that differ only by the cell address. So let's say we enter the wrong data in a cell with a formula. If we delete that data, we also delete the underlying formula. My solution is to merely copy the formula from an adjacent cell on the line above or below. This works well if the cell happens to be blank (except for the hidden formula). If the adjacent cell is not blank, this approach might still work (I'm not sure). In any case, it may be possible to find a blank cell somewhere else in the column that contains a version of the formula. Just copy and paste from a "blank" cell containing an appropriate formula, and the formula should adjust to the new cell address. You've effectively deleted your data and retained the formula.
 
Back
Top