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
<tbody>
</tbody>
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
Date | Identifier | ratio | total | expense | Income | |||
01/09/17 | 0 | 1 | 1,734,832,798.05 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
01/09/17 | 1 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
01/09/17 | 2 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
01/09/17 | 3 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
01/09/17 | 4 | 0.4 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
04/09/17 | 0 | 1 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
04/09/17 | 1 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
04/09/17 | 2 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
04/09/17 | 3 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
04/09/17 | 4 | 0.2 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
05/09/17 | 0 | 1 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
05/09/17 | 1 | 0.5 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
05/09/17 | 2 | 0.5 |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody> </tbody><colgroup><col></colgroup> | |||
<tbody>
</tbody>