Forum Discussion

Deleted's avatar
Deleted
Aug 01, 2018

Date and values

Hi

 

I have the following formula: =if(N21=today();$B$4;""). in cell O21 Where n21 is for example 01-08-1028.

And the value of b4 is every day different.

My problem is that when I use the formule tomorrow (O2-08-2018) the value of 01-08-2018 is blank again. But that value needs to remain in the cell behind 01-08-2018.

how do I fix this problem

 

excel

4 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 02, 2018

    Henk, if you mean to pick-up B4 value for today and when keep it unchanged on next days, afraid that doesn't work, at least without VBA programming.

     

    • Deleted's avatar
      Deleted
      Aug 14, 2018

      That exact the problem.

      I have no VBA programming skils

       

      • Philip West's avatar
        Philip West
        Steel Contributor
        Aug 14, 2018

        Hi Hank,

        here's 1 way to fix it.

         As always with VBA, back up your workbook! There is no 'Undo' with these scripts.

         

        Open up the VB editor and add a module.

         

        Sub conditional_replace()

        start_row = 1

            For i = start_row To ActiveSheet.Cells(ActiveSheet.Rows.Count, "o").End(xlUp).Row
                If ActiveSheet.Range("o" & i).Value <> "" Then
                    ActiveSheet.Range("o" & i).Value = ActiveSheet.Range("o" & i).Value
                End If
            
            Next

        End Sub

        Add this code, save and then from the developer tab click Insert, pick the button, draw a button where you want it and then pick conditional_replace from the options. Each time you press the button it will run down column 'o' and replace all the cells that have something in it with just value.

         

        The only problem with this I can see is that if you miss a day your formula will go back to the 'false' result.

Resources