Trying to get date to appear when cell is changed, but not update daily

[JesseMS]

Hi,

I have a worksheet that I want when a cell has a value entered (in this case "Sendforsigning"), the date will populate in another cell.

In my current worksheet, when the above value is entered in one of the columns G - J, the date will appear in column K. I don't want to use VBA for this (many users who could break it), and I have managed to get a similar formula working on another spreadsheet, using only one condition.

I also want the date to be removed when the value is removed, but that's not as important.

My current formula is:

=IF(AND(K72="",OR(G72="Sendforsigning",H72="Sendforsigning",I72="Sendforsigning",J72="Sendforsigning")),TODAY(),"")

But I get a blank cell from it. Am I doing something wrong?

Thanks in advance

 

[Alex Blakenburg]

At the moment you seem to be saying you have the formula in K72 but the formula is in K72. That is a circular reference and will not work. Please clarify.

But not update daily

That is not going to be possible without using VBA. Today() is always going to change the date to today's date.

 

[JesseMS]

At the moment you seem to be saying you have the formula in K72 but the formula is in K72. That is a circular reference and will not work. Please clarify.

That is not going to be possible without using VBA. Today() is always going to change the date to today's date.

Hi Alex,

The formula is in K72, and I'm trying to get the result to be in K72.

The formula I have working on another sheet is:

=IF(B58<>"",IF(D58="",TODAY(),D58),"")

Which is located in D58, it is a circular reference, but it does work. It provides the date when the relevant cell in column B is filled, while not updating the date daily. Not a perfect solution, but it works for my situation.

I'm just trying to expand that for the above formula.

Thanks

 

[Alex Blakenburg]

I am sorry but I expect that the fact that is circular reference is probably influencing its updating behaviour.
There are some specific uses for circular references for iterative applications but in normal use it is just like a time bomb.

You will worry most users more by having a spreadsheet with a circular reference in it than by having an Event Macro. A circular reference would indicate to most people that the spreadsheet can't be relied on.

 

[JesseMS]

I am sorry but I expect that the fact that is circular reference is probably influencing its updating behaviour.
There are some specific uses for circular references for iterative applications but in normal use it is just like a time bomb.

You will worry most users more by having a spreadsheet with a circular reference in it than by having an Event Macro. A circular reference would indicate to most people that the spreadsheet can't be relied on.

Fair enough.

I'm still a beginner at most things Excel, even though I seem to be a fair bit more advanced than most others at my workplace.

If this was done through a macro, how would that look?

Thanks

 

[Alex Blakenburg]

Give this a try on a copy of your workbook.
You will need to save the workbook as xlsm

On the Name Tab of the sheet on which you have the formula
Right Click and select View Code
In the code window that pops up copy the below

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 _
        And Not Intersect(Target, Columns("G:J")) Is Nothing _
        And Range("K" & Target.Row) = "" Then
        
        If Application.CountIfs(Columns("G:J").Rows(Target.Row), "Sendforsigning") > 0 Then
            Application.EnableEvents = False
            Range("K" & Target.Row) = Date
            Application.EnableEvents = True
        End If
    End If

End Sub

If you have never used VBA you may need to turn on the developers tab, this link has the instructions.
Enable Developer Tab in Excel - wellsr.com