So, with a help from great excel experts in this forum, I was able to get the formula that would give me the results for STEP 1. Now, I need help for STEP 2. F
or an example, I want ABC's Session 2 for Feb-17 to be the net value of from both Jan-17 and Feb-17, which would equal to ZERO. Then for Mar-17, ABC's Session 2 would become 12.
Another example would be for ABC's Session 3 for Feb-17 would be 230, netting/accumulating both Jan and Feb. Then for Mar-17, it would become 242, all of Jan, Feb, and Mar.
I feel like "<="&EOMONTH should be used somewhere, but not quite sure how and where I would insert it... Greatly appreciate any help!!!
<tbody>
</tbody>
<tbody>
</tbody>
B15=INDEX($C$2:$E$10,MATCH($A15,$A$2:$A$10,0)+MATCH(B$14,$B$2:$B$4,0)-1,MATCH(B$13,$C$1:$E$1,0))
or an example, I want ABC's Session 2 for Feb-17 to be the net value of from both Jan-17 and Feb-17, which would equal to ZERO. Then for Mar-17, ABC's Session 2 would become 12.
Another example would be for ABC's Session 3 for Feb-17 would be 230, netting/accumulating both Jan and Feb. Then for Mar-17, it would become 242, all of Jan, Feb, and Mar.
I feel like "<="&EOMONTH should be used somewhere, but not quite sure how and where I would insert it... Greatly appreciate any help!!!
A | B | C | D | E | |
1 | 17-Jan | 17-Feb | 17-Mar | ||
2 | ABC | Session 1 | – | -50 | – |
3 | ABC | Session 2 | -15 | 15 | 12 |
4 | ABC | Session 3 | 30 | 200 | 12 |
5 | BCD | Session 1 | 400 | – | -300 |
6 | BCD | Session 2 | -50 | 100 | 150 |
7 | BCD | Session 3 | – | -50 | – |
8 | CDE | Session 1 | 16 | 300 | – |
9 | CDE | Session 2 | 50 | 100 | 15 |
10 | CDE | Session 3 | 90 | -10 | 5 |
<tbody>
</tbody>
A | B | C | D | E | F | G | H | I | J | |
12 | Input Sheet | |||||||||
13 | 17-Jan | 17-Feb | 17-Mar | |||||||
14 | Session 1 | Session 2 | Session 3 | Session 1 | Session 2 | Session 3 | Session 1 | Session 2 | Session 3 | |
15 | ABC | – | -15 | 30 | -50 | 15 | 200 | – | 12 | 12 |
16 | BCD | 400 | -50 | – | – | 100 | -50 | -300 | 150 | – |
17 | CDE | 16 | 50 | 90 | 300 | 100 | -10 | – | 15 | 5 |
<tbody>
</tbody>
B15=INDEX($C$2:$E$10,MATCH($A15,$A$2:$A$10,0)+MATCH(B$14,$B$2:$B$4,0)-1,MATCH(B$13,$C$1:$E$1,0))