Morning all.
I am trying to sort out a macro for copy/paste columns of formula.
As each column is copied and moved to the next, the formula needs to update to reflect it's new location.
Starting cell is C3. The data set is 24rows x 60colums. (ie. 24hrs x 60 days. Copy C3 - C26 and paste one column at a time through to BJ3-BJ26)
As each column is pasted, the formula should incrementally update one part of the formula.
Formula in C3 is :
Formula in C4 is :
...
Formula in C26 is :
This is pulling data from various locations for each hour of TODAY.
The copy paste for column D to BJ (60days) should update like this :
so, TODAY()-1 is yesterdays data in column D, for 24hrs.
Column E would be TODAY()-2, Column BJ would be TODAY()-59, for a total of 60 days data.
I have tried recording the macro, but it turns out to be massive and clunky looking. I just don't have the experience/knowledge to simplify it and make it elegant.
Thanks in advance.
Martin
I am trying to sort out a macro for copy/paste columns of formula.
As each column is copied and moved to the next, the formula needs to update to reflect it's new location.
Starting cell is C3. The data set is 24rows x 60colums. (ie. 24hrs x 60 days. Copy C3 - C26 and paste one column at a time through to BJ3-BJ26)
As each column is pasted, the formula should incrementally update one part of the formula.
Formula in C3 is :
Code:
=COUNTIFS(DATA!$A$15:$A$40000,"="&TODAY(),DATA!$H$15:$H$40000,[B]"=0"[/B],DATA!$D$15:$D$40000,"=12")
Code:
=COUNTIFS(DATA!$A$15:$A$40000,"="&TODAY(),DATA!$H$15:$H$40000,[B]"=1"[/B],DATA!$D$15:$D$40000,"=12")
Formula in C26 is :
Code:
=COUNTIFS(DATA!$A$15:$A$40000,"="&TODAY(),DATA!$H$15:$H$40000,[B]"=23"[/B],DATA!$D$15:$D$40000,"=12")
The copy paste for column D to BJ (60days) should update like this :
Code:
=COUNTIFS(DATA!$A$15:$A$40000,"="&[B]TODAY()-1[/B],DATA!$H$15:$H$40000,"=0[B]"[/B],DATA!$D$15:$D$40000,"=12")
so, TODAY()-1 is yesterdays data in column D, for 24hrs.
Column E would be TODAY()-2, Column BJ would be TODAY()-59, for a total of 60 days data.
I have tried recording the macro, but it turns out to be massive and clunky looking. I just don't have the experience/knowledge to simplify it and make it elegant.
Thanks in advance.
Martin