Enter data in cell, but keep formula as well.

[ultrawat]

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.....

 

[Blade Hunter]

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

 

[ultrawat]

Thanks for the reply.

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......

 

[Blade Hunter]

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

 

[Blade Hunter]

If so, put this at the worksheet level:

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

 

[ultrawat]

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

 

[Blade Hunter]

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

 

[ultrawat]

Thanks Dan,

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

Fantastic...works a treat!

 

[ultrawat]

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

 

[Blade Hunter]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
    Application.EnableEvents = False
    Range("C1").Formula = "=A1*B1"
End If
Application.EnableEvents = True
End Sub

Cheers

Dan