Hello everybody, I'd greatly appreciate some help
I’d ideally like to have some cells that can have a formula which can be overwritten with a manual entry, but that reverts back to the formula if the value is deleted/cleared.
E.g, in the following…
‘Product’, in column A, is a text, entered with a list validation
‘Unit cost’, in column B, is derived from A1 with a vlookup
‘Quantity’, in column C, is manually entered, and
‘Price’, in column D, is B*C
…I would really like to have it so I can either generate B and D using manual entries in A and C, or overtype them, but also have them revert back to the formula if A is deleted/cleared.
I’ve been searching around the forum here, and I wondering if there is some kind of worksheet event that could be used to trigger the reverting.
Should it be along these lines?:
(You can see I’m a bit clueless with coding, but hopefully there’s enough to get the gist of what I’m trying to do)
There’s a sort of similar discussion here:
http://www.mrexcel.com/board2/viewt...rder=asc&highlight=overwrite+formula&start=10
...but I don’t really understand it, since it sort of goes in a different direction.
Any help much appreciated
I’d ideally like to have some cells that can have a formula which can be overwritten with a manual entry, but that reverts back to the formula if the value is deleted/cleared.
E.g, in the following…
‘Product’, in column A, is a text, entered with a list validation
‘Unit cost’, in column B, is derived from A1 with a vlookup
‘Quantity’, in column C, is manually entered, and
‘Price’, in column D, is B*C
…I would really like to have it so I can either generate B and D using manual entries in A and C, or overtype them, but also have them revert back to the formula if A is deleted/cleared.
I’ve been searching around the forum here, and I wondering if there is some kind of worksheet event that could be used to trigger the reverting.
Should it be along these lines?:
Code:
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$D$1" Then
If Target.Value =0 Then
Range("D1").Value = B1 * C1
End If
End If
End Sub

There’s a sort of similar discussion here:
http://www.mrexcel.com/board2/viewt...rder=asc&highlight=overwrite+formula&start=10
...but I don’t really understand it, since it sort of goes in a different direction.
Any help much appreciated