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