Keep the data even after the date change [Solved]

Question

Keep the data even after the date change [Solved]

Report

kal7667

Posts: 1
Registration date: Saturday August 29, 2020
Status: Member
Last seen: August 29, 2020

- Aug 29, 2020 at 04:24 AM
TrowaD

Posts: 2691
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 14, 2021

- Sep 1, 2020 at 11:54 AM

Hello,

i have 2 sheets one is for the data called employee cost which is having a calculation of employees EOS and the other sheet is a report sheet quarterly basis retrieve the date from the first sheet based on formula below as below

Sample of quarterly report

DATE EOS LEAVE TOTAL
26-Aug-20 1,797,331.23 653,240.56 2,450,571.79
30-Jun-20 - - -
30-Sep-20 - - -
31-Dec-20 - - -

the formula in column EOS is =IF(TODAY()=C4(date first cell),'EMPLOYEE COST 2020'!$AF$128,0)

as you notice the criteria based on date means when the condition is true the figures will shows up, but next day it will be gone because the date changed( Today()), therefore I need to keep the data showed in that specific date and not change by date changes.

thanks in advance

kal

Keep the data even after the date change
How to change system update from wifi to mobile data - How-To - Android
Amd data change update new data to dmi ✓ - Forum - Windows 10
How to change ip address on android mobile data - How-To - Android
How to change whatsapp number without losing data - How-To - WhatsApp
Change data format excel - How-To - Excel

1 reply

Reply Ask a question

TrowaD · Answer 1 · 2020-09-01T11:54:16-0400

Hi Kal,

The code below will change the formula's on your Report sheet (Change sheet name in code to match yours if necessary) to values when you open your workbook.

Here is the code:

Private Sub Workbook_Open()
Sheets("Report").Select 'Change the sheet name to match yours
For Each cell In Range("A4:A" & Range("A4").End(xlDown).Row)
    If cell.Value = Date Then
        cell.Offset(0, 1).Value = cell.Offset(0, 1).Value
        cell.Offset(0, 2).Value = cell.Offset(0, 2).Value
        cell.Offset(0, 3).Value = cell.Offset(0, 3).Value
    End If
Next cell
End Sub

To use the code, press Alt+F11, double-click ThisWorkbook on the left side and paste the code in the big white field. Save and close your workbook, re-open to see the result of the code.

Here is a picture to guide you:

Best regards,
Trowa

Keep the data even after the date change [Solved]

1 reply

Your reply

Subscribe To Our Newsletter!