Conditional Self-Referencing Formula (only execute if cell value == "")

Question

Hello, here is my problem with the execution of an excel formula.

These are the requirements:

• I have several lines in an excel sheet.
• At the end of each line there is a field called "Month".
• If someone enters new values in this line, the current month should be inserted.
• If the values get modified, the month must not be changed.

To include the current month, I use some formula like this (A10 is just a random field which will be filled):

=IF(A10<>0; MONTH(NOW()); "")

Now I need some condition to check if the field contains a month allready, and if it is so, do nothing. I tried the following (A15 should contain the month):

=IF(A15 <> ""; IF(A10<>0; MONTH(NOW()); "") ; )

To decide if the field should be changed by the formular, I have to check its current value. The problem is, that this will end up in an circular reference to the field itself.

Has anyone a working solution for this kind of problems? I try not to use any VBA code in there.

Answer 1

You can't do it with formulas, you have to use VBA. The formula can't refer to itself.

The other problem is that NOW will change every time you recalculate. So even with your first formula, the month will not remain static.

If you decide you can live with VBA, right click on the sheet tab and choose View Code. The code you'll need will look something like this

Private Sub Worksheet_Change(ByVal Target As Range)

    'We only care if cells are changed in a certain range
    If Not Intersect(Target, Me.Range("A1:G10")) Is Nothing Then

        'Only do this if there's nothing in row 15 for whatever column
        'was changed
        If IsEmpty(Me.Cells(15, Target.Column).Value) Then

            'Put the month in row 15 of the current column
            Me.Cells(15, Target.Column).Value = Month(Now)
        End If
    End If

End Sub