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)

Answers


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

  1. If C4 is empty, it will be sety equal to 12
  2. 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

Need Your Help

Can I have git send out an email with the post-receive-email scripts if my repo is on a Windows machine?

windows git email

I have seen a bunch of examples for sending emails out when a repo receives a new push, but they seem to all be for a repo on a linux machine, is this possible to do with my repo on a windows machi...

Active admin not finding partial in show

ruby-on-rails ruby-on-rails-4 activeadmin

This should be pretty simple, trying to render a partial on active admin in show action: