Forum Discussion
Autocopy data from a cell to another cell based on current date
Hello,
I have two tables in one sheet which I wanted to use for monitoring daily gain/ loss data.
The =TODAY() formula is in table 1 (cell B3), as well as the data from a specific cell (J17) that I want to copy everyday to table 2.
Table 2 looks like this:
Cell J7 changes everyday.
So, what formula can I use to auto copy the cell J7 on a daily basis and update the Table 2 automatically?
Hi
Ok - I understand the problem now.
Using TODAY() will never work as you already spotted when you move to the next day the previous days data is overwritten.
The question I have for you is how important is for you to have an automated process to achieve this? It seems to me simpler for you to copy/paste the value in the appropriate cell in Table 2?
What you want can be achieved via some VBA coding - but I think it is using a sledgehammer cracking a nut!
I attach an example which includes some very crude code to copy cell J17 to the cell for Feb 18 (p27).
If you clear the date in cell P27 and go to another sheet (sheet3) and then select sheet2 cell P27 will be populated with J17 data.
At the moment this only works for cell P27. The code would need to be amended to find the appropriate cell each day (based on the current date) in order to paste the data.
I think this is too complicated to achieve - a simple copy / paste each day is simpler.
sorry!
Peter
5 Replies
In the example you have the date as Feb 10. Where on sheet 2 do you want to copy the value of J17?
Can you share your file?
thanks
Peter
Hello, peteryac60!
Thank you for checking my query.
Please see the attached file. This is more or less how the data will looks like, but for confidentiality, I replaced the content, so please do not mind if there are miscalculations as I just input whatever came to mind.
Anyway, there are 2 tables on the same sheet.
In Table 1, the percentage data (10.59%) on cell 'J17' is reflected on the cell 'P27' on Table 2.The condition is if the date is Today (cell 'B3'), the corresponding date on Table 2 should automatically update/ copy the value on J17. The variable is the value on J17 coz that changes everyday.
Simply put, since today is February 18, 2021, cell 'P27' (which is Feb. 18 as well) copied the 10.59% from J17. On Feb. 19, 'P27' data must not change, but P28 should copy the new value on J17 instead.
My current formula is =IF($B$3=TODAY(),$J$17,0), but by using this, if the day changes to Feb. 19, 2021, cell 'P27' will be the same with cell 'P28.' I want it to stay as 10.59%, so I could see how the "Portfolio Gain" is progressing on a daily basis.
I hope my explanation didn't confuse you, and I also hope you could help me come up with a formula to automate this data. Looking forward to your reply.
- GervieHi
Ok - I understand the problem now.
Using TODAY() will never work as you already spotted when you move to the next day the previous days data is overwritten.
The question I have for you is how important is for you to have an automated process to achieve this? It seems to me simpler for you to copy/paste the value in the appropriate cell in Table 2?
What you want can be achieved via some VBA coding - but I think it is using a sledgehammer cracking a nut!
I attach an example which includes some very crude code to copy cell J17 to the cell for Feb 18 (p27).
If you clear the date in cell P27 and go to another sheet (sheet3) and then select sheet2 cell P27 will be populated with J17 data.
At the moment this only works for cell P27. The code would need to be amended to find the appropriate cell each day (based on the current date) in order to paste the data.
I think this is too complicated to achieve - a simple copy / paste each day is simpler.
sorry!
Peter
