Q:

Formula only if cell not empty

I'm trying to do a formula that will fill the cell value, but only once, when the cell is empty. Once filled I want the value to stay as is, even though cells that are part of the formula changed.
ie
C: =VLOOKUP(Cx,'A$1:B$3,2,FALSE)

Originaly
A       B      C
John    1      1
Bob     4      4
Jim     6      6

After Changing Jim's B value
Jim     10      6

So I want the VLOOKUP to fetch a value only if one isn't there already. I tried to do it with an IF, but I got a circular reference error:
=IF(C1= "", VLOOKUP(C1,'A$1:B$3,2,FALSE),C1)

A:

I think this is along the lines you want

right-click your sheet tab
View Code
copy and paste in the code below
press alt & f11 to get back to Excel

If you enter say 12 in B4 and A4 is not blank then

If C4 is empty, it will be sety equal to 12
if C4 has a value, it is retained

code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Intersect(Columns("B:B"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng2 In rng1
If rng2.Offset(0, -1).Value <> vbNullString Then rng2.Offset(0, 1).Value = rng2.Value
Next
Application.EnableEvents = True
End Sub

