I had a similar question about a month ago, but it's just different enough to be causing me headaches...
What I am trying to do is develop a formula for the shaded cells on Sheet1 that returns the cell values by Unique ID, matched by month, from Sheet2 (I've left the desired results in the shaded area for illustration). As you can see, the results will come in a "waterfall" pattern on Sheet1 due to the Unique IDs staggered monthly Issue Dates. But, the source data (Sheet2) is arranged based on the life-cycle of each Unique ID (Col. 0 = Issue Date minus 1 Month, Col. 1 = Issue Date Month, and Col. 2 = Issue Date plus 1 Month, etc.).
Although this is an extremely pared-down version of my actual problem, I think I've provided enough info here, but please let me know if you need more... thanks in advance for any and all help!
Sheet1:
Sheet2:
What I am trying to do is develop a formula for the shaded cells on Sheet1 that returns the cell values by Unique ID, matched by month, from Sheet2 (I've left the desired results in the shaded area for illustration). As you can see, the results will come in a "waterfall" pattern on Sheet1 due to the Unique IDs staggered monthly Issue Dates. But, the source data (Sheet2) is arranged based on the life-cycle of each Unique ID (Col. 0 = Issue Date minus 1 Month, Col. 1 = Issue Date Month, and Col. 2 = Issue Date plus 1 Month, etc.).
Although this is an extremely pared-down version of my actual problem, I think I've provided enough info here, but please let me know if you need more... thanks in advance for any and all help!
Sheet1:
PPC ME Example.xls | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | UniqueID | IssueDate | 08/01/06 | 09/01/06 | 10/01/06 | 11/01/06 | 12/01/06 | 01/01/07 | ||
2 | 12345 | 09/01/06 | 2 | 10 | 23 | 54 | ||||
3 | 23456 | 09/01/06 | 3 | 8 | 16 | 45 | ||||
4 | 34567 | 10/01/06 | 2 | 11 | 19 | 43 | ||||
5 | 45678 | 11/01/06 | 1 | 15 | 22 | 39 | ||||
Sheet1 |
Sheet2:
PPC ME Example.xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | UniqueID | IssueDate | 0 | 1 | 2 | 3 | ||
2 | 12345 | 09/01/06 | 2 | 10 | 23 | 54 | ||
3 | 23456 | 09/01/06 | 3 | 8 | 16 | 45 | ||
4 | 34567 | 10/01/06 | 2 | 11 | 19 | 43 | ||
5 | 45678 | 11/01/06 | 1 | 15 | 22 | 39 | ||
Sheet2 |