Hi,
Not sure if im going to explain this correctly or over-complicating but hoping someone can help
I have a macro that copies data into an excel from column A to D. In column B will be identifiers that will drive the formulas. (date might be needed but not sure)
Example we copy in data from another location in columns A to D as below. We would like the macro to do the following:
For every identifier 0 which is in column B would like the following formulas in expense & income row:
Expense (Column E)
=SUMIFS(Sheet2!F:F,Sheet2!P:P,Sheet1!A2)
Income (Column F)
=SUMIFS(Sheet2!H:H,Sheet2!P:P,Sheet1!A2)
Then for the identifier in between the zeros would like the totals (row with identifier 0) multiplied by ratio. For example:
Cell E3 will have formula =C3*$E$2
Cell E4 will have formula = =C4*$E$2
Cell F3 will have formula = =$F$2*C3
Cell F4 will have formula = =$F$2*C4
These formulas will drag down for all identifiers for that date say 01/09/17.
Then say on 04/09/17 we will input the sumif formulas for identifier 0
Then under identifier 0 we will need to input the formulas again as above except the referenced cell will be now identifier 0 from the 04/09/17.
Does that make sense? Can macro be built for this
Example output should be as follows basing on the totals pulled in by the sumifs etc
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Identifier[/TD]
[TD]ratio[/TD]
[TD]total[/TD]
[TD]expense[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1,734,832,798.05[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-247531.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 3,131,045.77[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]1[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 346,966,559.61 [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-49506.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 626,209.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]2[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 346,966,559.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-49506.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 626,209.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]3[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 346,966,559.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-49506.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 626,209.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]4[/TD]
[TD]0.4
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 693,933,119.22[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-99012.46[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,252,418.31[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 1,795,304,394.68[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-266461.87[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 3,165,470.16[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]1[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-53292.374[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 633,094.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]2[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-53292.374[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 633,094.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]3[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-53292.374[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 633,094.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]4[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-106584.75[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,266,188.06[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]05/09/17[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 1,699,286,162.05[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-272588.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 3,178,645.21[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]05/09/17[/TD]
[TD]1[/TD]
[TD]0.5[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD] 849,643,081.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-136294.12[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,589,322.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]05/09/17[/TD]
[TD]2[/TD]
[TD]0.5[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD] 849,643,081.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-136294.12[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,589,322.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Not sure if im going to explain this correctly or over-complicating but hoping someone can help
I have a macro that copies data into an excel from column A to D. In column B will be identifiers that will drive the formulas. (date might be needed but not sure)
Example we copy in data from another location in columns A to D as below. We would like the macro to do the following:
For every identifier 0 which is in column B would like the following formulas in expense & income row:
Expense (Column E)
=SUMIFS(Sheet2!F:F,Sheet2!P:P,Sheet1!A2)
Income (Column F)
=SUMIFS(Sheet2!H:H,Sheet2!P:P,Sheet1!A2)
Then for the identifier in between the zeros would like the totals (row with identifier 0) multiplied by ratio. For example:
Cell E3 will have formula =C3*$E$2
Cell E4 will have formula = =C4*$E$2
Cell F3 will have formula = =$F$2*C3
Cell F4 will have formula = =$F$2*C4
These formulas will drag down for all identifiers for that date say 01/09/17.
Then say on 04/09/17 we will input the sumif formulas for identifier 0
Then under identifier 0 we will need to input the formulas again as above except the referenced cell will be now identifier 0 from the 04/09/17.
Does that make sense? Can macro be built for this
Example output should be as follows basing on the totals pulled in by the sumifs etc
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Identifier[/TD]
[TD]ratio[/TD]
[TD]total[/TD]
[TD]expense[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1,734,832,798.05[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-247531.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 3,131,045.77[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]1[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD] 346,966,559.61 [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-49506.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 626,209.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]2[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 346,966,559.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-49506.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 626,209.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]3[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 346,966,559.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-49506.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 626,209.15[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]01/09/17[/TD]
[TD]4[/TD]
[TD]0.4
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 693,933,119.22[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-99012.46[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,252,418.31[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 1,795,304,394.68[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-266461.87[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 3,165,470.16[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]1[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-53292.374[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 633,094.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]2[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-53292.374[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 633,094.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]3[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-53292.374[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 633,094.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]04/09/17[/TD]
[TD]4[/TD]
[TD]0.2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 359,060,878.94[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-106584.75[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,266,188.06[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]05/09/17[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD] 1,699,286,162.05[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-272588.23[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 3,178,645.21[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]05/09/17[/TD]
[TD]1[/TD]
[TD]0.5[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD] 849,643,081.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-136294.12[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,589,322.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]05/09/17[/TD]
[TD]2[/TD]
[TD]0.5[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD] 849,643,081.03[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]-136294.12[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD] 1,589,322.61[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
