Forum Discussion
record data from a specific cell at the same time once per day?
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!
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)
13 Replies
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?
HansVogelaar 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.
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.
