2

how can I add the contents of the cell A to the cell B, if cell B is empty in MS Excel 2010?

My situation in detail:

  • Column A (column with some empty and some non-empty values): AD
  • Column B (no empty values here): I

Now I need Excel to fill the column AD with the values from the column I, if a value in the column AD is missing. Otherwise the original value from the column AD should remain.

Note: All columns contain text data (no numbers, currency etc.)

My approach:

=IF((AD3)="";I3;AD3)

But this doesn't work as expected: it just throws a circular reference error and fills the column AD3 with "0" in spite of any other values.

Does anyone have an idea? Thank you.

1

You're getting a circular reference because the cell only holds one piece of information. Currently, using row 3 as an example, AD3 contains a value (lets say "Hello"). As soon as you try and put a function in, AD3 now contains the function and "Hello" is completely gone. Now it'll keep trying to pull up the function referencing AD3 infinitely.

You'd be better off using a helper column for this if you want to use a formula, otherwise you'll need to hard code it. Personally, I'd add a new column AE, hide the column AD, and use the formula you've used in AE.

Other options you have are to run a VBA macro across it, something like this:

Sub FillBlanksFromI()
    Dim j
    For j = 1 To FindLastRow("AD")
        If IsEmpty(Range("AD" & j).Value) Then
            Range("AD" & j).Value = Range("I" & j).Value
        End If
    Next j
End Sub

Function FindLastRow(col As String) As Long

    Dim lastRow As Long
    lastRow = ActiveSheet.Range(col & Rows.Count).End(xlUp).Row
    FindLastRow = lastRow

End Function

... which will go through an fill any blank fields with their value from I. This WILL NOT automatically update, and would only work once.

Alternatively, you need to hard code each function in each cell to either be the value, or =I3

  • Thank you very much, using a helper column did the trick. I created a new column AE3 and modified the formula as follows: =IF((AE3)="";I3;AD3) and now it aggregates values from both columns AD3 and I3 - just fine for me for the moment. – QB new media Mar 24 '16 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.