Autocopy data from a cell to another cell based on current date

[Gervie]

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? 

[#2]

@Gervie 

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

[#3]

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. 

- Gervie 

[#4 · Accepted solution]

@Gervie 

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]

Hi again, @peteryac60!

I understand.

For now, since this is for personal use, I could go on without the automation, but I might encounter the same for work purposes in the future. Actually, copy-pasting the previous data on a daily basis is exactly what I am doing in the sheets. I've been thinking all this time that there might be a simple formula that I could use, but at least now, I know that this is more complicated than I thought, and it would require me a VBA coding. I tried your instruction to clear P27, go to sheet 3, and rechecked P27. Indeed, the cell was auto-populated, and I'm amazed.  :) 

I guess I'll stick with manual copy-paste while I'm trying to learn more on VBA, and gain more skills like yours. Really appreciate your time and effort to respond. Thanks a lot, Peter! Cheers, and stay safe. :)

[#6]

@Gervie 

Hi

In order to access VBA you will need to add the developer tab to your ribbon.

Select anywhere on the ribbon and right click. You should see 'customize the ribbon' option. Select this and a window will pop up. Under the main tab section (a little more than halfway) is a 'developer' option. Check the box.  Then hit OK. The developer tab will appear on the ribbon. This is just the fist step to VBA!

Get yourself a simple VBA book or look at YouTube - thee are some good tutorials there.

BTW - I am not sure if you noticed but the file I sent you had a "xlms" suffix as opposed to "xlsx". VBA will only work on "xlms" files!

If you are happy with the 'solution' I provided can you please mark this as complete.

many thanks and good luck!

Peter