Capture Value from Changing Cell

[Guy27B]

I have set up a spreadsheet with a dashboard. Cell A5 contains a percentage that changes on a daily basis. I would like to capture the value of A5 on the 1st of each month and put it in a table and then create a line graph from this table. Is there a formula I can use to capture A5's value, and put it in a table where it is fixed and doesn't change when A5 changes? Thanks

 

[Joe4]

Welcome to the Board!

I think that would require VBA.
We can help you with that, if you provide the following information.
- Is this file being manually opened every day? Is it guaranteed to be manually opened on the first of every month?
- What cell should the value from A5 be copied to?
- Does the date also get populated in some cell (if so, which one)?

 

[Guy27B]

Thank you for your reply and kind consideration. Unfortunately I would prefer a formula if it is possible as I've not used VBA before and the file type is xlsx and changing it would mean I would have to change a Power Automate flow as well. However, if VBA is the only solution, then I'll do that.
Other questions:

1. There is no planned schedule to open this file every day, or on the first of every month. I am hoping to set up a formula that will perform automatically without the requirement to open the spreadsheet. Then, when the administrator wants to open the spreadsheet, the data and graph is updated automatically already.
2. I have two spreadsheets: The first is called 'Dashboard' and A5 contains the % data that will change. The second sheet is called 'Graph' and I'm hoping to create the table here, from which I can create the line graph. I'm currently using colum A for dates (e.g. A2 = 1 Mar 22, A3 = 1 Apr 22, A4 = 1 May 22, etc and I'm using Colum B to capture the values from 'Dashboard' A5 cell. So the idea is to put the value from A5 into the 'Graph' B2 cell on 1 Mar 22, and the value for A5 into the 'Graph' B3 cell on 1 Apr 22, etc.
3. Yes, dates are in colum A in worksheet 'Graph'.

thanks

 

[Joe4]

The issues with not using VBA are the following:
- Any Excel cell can either have a formula, or a hard-coded value in them, but never both at the same time, and you cannot switch from one to the other without some sort of manual interaction or VBA.
- Excel formulas can only return values to the cell that they exist in. An Excel formula CANNOT write/return a value to a different cell.
- Nothing can happen in closed Excel workbooks without VBA.

Not using VBA before should not be too much of a concern, because we would just give you the VBA code you need, and might just really be a "plug-and-play" type of situation.
However, I do have some concerns regarding the timing, opening of files, and updating.

Where is cell A5 getting this percentage that is changing on a daily basis?
Is it a link or a formula? If so, can you post that formula?

If this is changing automatically, does that mean if you do not open the file on the 1st, but open the file on the 2nd, you will have already "missed" what the value was on the 1st, and there is no way to get that?
If that is true, then we will NEED to open the file on the first to get the value we need and update your table.
I would recommend using a program like Windows Scheduler to schedule a job to open the file on the 1st of every month, and then run your table update (via VBA).

Does this all sound feasible?
If so, please provide the answers to the questions I asked, and we can see what we can do for you.

 

[Guy27B]

Ah, that makes so much sense. Thank you. I'm really interested in what an Excel VBA solutions look like now, but I'm wondering if a more parsimonious solution would be to create a calendar reminder in Outlook and manually just copy and paste the value on the 1st of every month. What do you think?

 

[Joe4]

The certainly is one way. The question then really comes down to if you want to automate it or not.
And what happens if the first is a weekend or a holiday?
Is someone available to manually update it on those days?

 

[Guy27B]

Thanks Joe4. I've a quick meeting now. I'll come back with the answers to your questions in an hour. Thanks.

 

[Guy27B]

Hi Joe4

Where is cell A5 getting this percentage that is changing on a daily basis?
Is it a link or a formula? If so, can you post that formula?

1. Cell A5 formula is ='Form Data'!$L2/'HR Data'!$I$2
2. Form Data L2 = =COUNTIF(I:I,Validation!$C$4)
3. Validation C4 = text "Compliant"
4. I:I = a table. Each cell contains 1 of 4 possible text strings. This formula counts the number of 'compliant'
5. HR Data I2 = =COUNTA(B:B)-1 (this counts the entries in column B to give me the total number of staff)

If this is changing automatically, does that mean if you do not open the file on the 1st, but open the file on the 2nd, you will have already "missed" what the value was on the 1st, and there is no way to get that?

Data comes into the Spreadsheet from 2 different sources:​

1. An MS Form - a Power Automate flow is used to grab the data from the Form every time the user clicks the form's Submit button, and places the data into the 'Form Data' tab in the spreadsheet​
2. An Agresso Report is generated every month with Staff data. This data sits in a separate Spreedsheet. HR update this spreadsheet every month at the start of the month. A Power Query grabs this data and inputs it into my Master Spreadsheet.​

As this data is changing automatically, I was hoping to create an automatic system to capture the A5 data on the 1st of every month so that the Administrator doesn't have to open the Spreadsheet up in order to capture A5's data. You are correct in saying that currently, if the Administrator fails to open the spreadsheet on the 1st of the month, then A5's value for that day has not been captured.​

​

If that is true, then we will NEED to open the file on the first to get the value we need and update your table.
I would recommend using a program like Windows Scheduler to schedule a job to open the file on the 1st of every month, and then run your table update (via VBA).

Okay. Thanks.


Does this all sound feasible?
Yup!

 

[Joe4]

OK, what you would use is a Workbook_Open event procedure in VBA, which is VBA code that is automatically triggered when the workbook is opened.
In order for this to work, it MUST be placed in the "ThisWorkbook" VBA module of this workbook. If you place the VBA code in any other VBA module, it will not fire automatically.

Private Sub Workbook_Open()

    Dim rng As Range
    Dim r As Long

'   Exit if today is not the first day of the month
    If Day(Date) <> 1 Then Exit Sub
    
'   Check to see if record has already been updated (if so, exit sub)
    Set rng = Sheets("Graph").Range("A:A")
    If Application.WorksheetFunction.CountIf(rng, Date) > 0 Then Exit Sub
    
'   Update Graph sheet with new record
    r = Sheets("Graph").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Graph").Range("A" & r).Value = Date
    Sheets("Graph").Range("B" & r).Value = Sheets("Dashboard").Range("A5").Value
    
'   Save changes
    ActiveWorkbook.Save
    
'   Close Excel
    Application.Quit
    
End Sub

If you want to test it out, you can temporarily change the day number in this line to today:

    If Day(Date) <> 1 Then Exit Sub

i.e.

    If Day(Date) <> 22 Then Exit Sub

Then save, exit, and re-open the workbook, and it should copy the date and value over.

So then you would have your scheduler open this workbook on this first day of every month.

A few other things to note:
1. If you are running this from another computer (or server), make sure that VBA is enable from there so it can run when it is opened.
2. This code is always there, and will always run every time the file is opened, but it checks to see if the current day is the first of the month and if a record for today's date already exists on the "Graph" sheet. If it does, it simply exits out of the code before making any updates.
3. You probably want to schedule this to run early in the morning. If someone were to open the file up on the first BEFORE the scheduler does, it will do the update and then close Excel on them. If they then open Excel a second time, it will remain open, as the update has already been run.

 

[Guy27B]

Joe4 is this really awesome. Works perfectly. I was able to change the date as you suggested and it updated as expected. I didn't know how to enter the VBA code, but found this website explained the basics really well (Events in Excel VBA).
Thank you so much for doing this. Excellent solution.

I've also managed to get around my issue of not wanting to change the file type as it would have meant updating the Power Automate flow. I saved a copy of the file to create a Macro enabled version. Then I changed the formula in the Macro version in Cell A5 to copy the value in the original Spreadsheet's Dashboard A5. So now I have two spreadsheets - one with your Macro code, which gives me my Graph and my original spreadsheet with my dashboard. All working perfectly.

Thanks so much for your help. You're a star!