SOLVED

record data from a specific cell at the same time once per day?

Copper Contributor

Hi, 

Sheet 1 of my workbook is a dashboard of equipment with various data about each machine.  In column z titled "availability" I have entered a "Y" or "N" to show if the machine is available or not.  If the machine is sold or not to be counted I left it blank.  Then at the bottom of column z in cell 127 I entered =COUNTA(Z2:Z126) to count the total number of rows with either a y or n and not count those without.  In cell z 128 I entered =COUNTIF(Z2:Z126,"Y") to count all cells with "Y".  In cell z 129 I entered =Z128/Z127 then changed number format to "percentage" and this monitors the availability percentage.  I am very happy with this because at a glance I can see what today's current availability is.  I would like on sheet 3 for column A to show the date, and this updated daily automatically say at any given time such as midnight so at the same time everyday it updates this sheet 3, and then in column b to show the percent value from cell z129 as it shows on the date in column A.  In other words for today for example, A1 would show 08/05/2021 and column B show the current percentage in cell z129 and tomorrow the spreadsheet would update itself to show tomorrow's date in A2 and whatever the percentage value in cell z129 at the same time tomorrow.  I would then like to graph the data so daily I can look at the graph and see if over the last say week or two, the availability has gone up or down and how it fluctuates.  Thankyou very much!

 

13 Replies

@Greg_Prophet1234 

If you want Sheet 3 to be updated automatically after midnight, you'd have to keep the computer on, Excel running and the workbook open 24 hours a day. Or would it be OK to update Sheet 3 when the workbook is opened, or when you activate Sheet 3? Or click a button to update it?

@Hans Vogelaar I think if it updated when you turned on the computer or opened the excel workbook and it updated then that would be awesome.  I'd prefer not to have to click a button but everyday to update it.  It also doesn't have to be an exact time but just used midnight as an example as I didn't want data collected everytime the cell changes percent availability as this would be maybe 10 times per day but rather once per day, a snapshot to compare one day to the next.  It would be ideal if it were the same time everyday, and perhaps midnight so at days end whatever that percentage available would be the snapshot of the day and start the new day.  

@Greg_Prophet1234 

See the attached sample workbook. It is a macro-enabled workbook, so you will have to allow macros when you open it.

The first time it is opened on a day, the Workbook_Open event procedure in the ThisWorkbook module will enter the previous day in column A in the next available row of Sheet 3, and the current value of Z129 (which should be the value it was at the end of the previous day) on Sheet 1 in column B.

If the workbook is reopened later on the same day, nothing will happen.

@Hans Vogelaar As soon as I opened your sample, it did exactly as you mentioned!  You can't imagine how much time I spent trying to figure this out.  Your sample is exactly what I was looking for. I spent much of the day learning by watching videos online to understand macros and what you were talking about... I'm still learning. So I tried to type in the exact macro as you created but it won't work on my spreadsheet and I don't know what I'm doing wrong. I attached it and if you are willing to please take a look and see what I'm doing wrong?

Also I just have two more questions please. If I deleted a column from sheet 1 or added, would the macro automatically adapt like cells on the spreadsheet will? For example if I had 5 columns and formulas in column 4 that used data from column 2 but deleted column 1, 2 becomes one and the spreadsheet automatically changes all the formulas accordingly. Also, the same if I deleted a couple of rows so that the percentage is no longer "z129" but say "z127" would the macro adjust or would I have to edit the macro myself? Thank-you very much!

best response confirmed by Greg_Prophet1234 (Copper Contributor)
Solution

@Greg_Prophet1234 

The problem was that the code used different worksheet names.

I have given cell Z129 on Sheet1 the name CurrenPercentage. If you insert or delete rows above it, or columns to the left of it, Excel will automatically adjust the definition of the name. The code now uses the name instead of the literal address Z129.

 

(I took the liberty to change some date formulas on Sheet1 to hide errors)

Thankyou very much indeed my friend!

@Greg_Prophet1234 Hans, I plotted a line graph on sheet 3 to show the data of the specific date with that day's availability.  I had to manually do this.  Is there way to have the graph update automatically when the spreadsheet updates itself in the columns A and B of sheet?  Is this done with a macro also?

I attached the spreadsheet as fresh so I can start from scratch and move forward nice and neat.

@Hans Vogelaar 

Hans, I plotted a line graph on sheet 3 to show the data of the specific date with that day's availability.  I had to manually do this.  Is there way to have the graph update automatically when the spreadsheet updates itself in the columns A and B of sheet?  Is this done with a macro also?

I attached the spreadsheet as fresh so I can start from scratch and move forward nice and neat.

@Greg_Prophet1234 

No, you don't need VBA for that.

In the attached version, I have created dynamic named ranges XValues and YValues - see Formulas > Name Manager for their definition.

I then changed the data source of the chart series to refer to XValues and YValues.

The chart will automatically expand as new data are added.

Ok thankyou so much for explaining that to me. I appreciate so much your help and explanations.
Hi there. Just wanna ask on How to make it record the latest update on the same day. Like for example in the morning it was 65% then became 87% in the afternoon. Your vba i try to use. Thank u

@Jesse985 

Could you attach a new version of the workbook with some dummy data that shows how the time of day comes into it?

And do you want to show only the latest update of each day, or all intermediate values?

Your vba is working. It takes data from the previous day. Thats all i need. Thank you!
1 best response

Accepted Solutions
best response confirmed by Greg_Prophet1234 (Copper Contributor)
Solution

@Greg_Prophet1234 

The problem was that the code used different worksheet names.

I have given cell Z129 on Sheet1 the name CurrenPercentage. If you insert or delete rows above it, or columns to the left of it, Excel will automatically adjust the definition of the name. The code now uses the name instead of the literal address Z129.

 

(I took the liberty to change some date formulas on Sheet1 to hide errors)

View solution in original post