# Enter data in cell, but keep formula as well.

#### ultrawat

##### New Member
I hope someone can help...

Is it possible to have a formula in a cell which can be overwritten by manually entering in a number, but if the manually entered number is deleted, the formula remains in the cell.

Thanks in anticipation.....

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Is it a static formula? You could use the change event and if the cell is blank then it puts the formula in.

Cheers

Dan

It would be a simple formula.
A simplified example would be....:
The spreadsheet has a formula which calculate simple areas. These might come to me as 8metres x 13metres ...or just as a total of 104 square metres.
I would enter 8 in A1, 13 in B1 and the answer 104 is calculated in C1 (A1*B1)
I would like to have the option of overwritting by manually entering 104, but if the measurments changed the formula would still remain in C1.

Hope this makes sense......

OK So just so we are on the same page here, You want to be able to type in C1 and this will be the value but if you enter something in to A1 or B1 you want C1 to be A1*B1 correct?

Cheers

Dan

If so, put this at the worksheet level:

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "\$A\$" & Target.Row Or Target.Address = "\$B\$" & Target.Row Then
Application.EnableEvents = False
Range("C" & Target.Row).Formula = "=A" & Target.Row & "*B" & Target.Row
End If
Application.EnableEvents = True
End Sub``````

That's it......

Or i might have a case where i find that the 104 I have manually entered into C1 is actually wrong, and I need to delete 104 from C1 and put new data into A1 (say 9) and B1 (say 11) and the answer in C1 shows as 99.

I think the change event idea is the go, but I am unsure how do do it.

Thanks again

The code I have posted will do exactly that.

You can enter whatever you like in to Column C (I did the whole columns not just row 1), This will remain so long as you don't enter anything else in to columns A or B.

Entering a new value in Columns A or B will recalculate Column C and overwrite what you entered there.

Cheers

Dan

Thanks Dan,

My last response was sent before I saw the code you sent.

Fantastic...works a treat!

One last question....

How would I limit the code to act on only one set of cell A1, B1 and C1, rather than the whole column?

Regards

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("C1").Formula = "=A1*B1"
End If
Application.EnableEvents = True
End Sub``````

Cheers

Dan

Replies
41
Views
464
Replies
2
Views
154
Replies
2
Views
85
Replies
7
Views
90
Replies
1
Views
82

1,202,990
Messages
6,052,962
Members
444,622
Latest member
Kriszilla

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back