gmooney
Active Member
- Joined
- Oct 21, 2004
- Messages
- 254
- Office Version
- 365
- Platform
- Windows
Having an issue with the following table that is bringing in data from 2 different sources and whose dates do not line up to the same date so I have to apply "some logic" to try to get them aligned.
Column B is a start date from one source while column D is data from another data source. As you can see in this example I found a total of 39 start dates in column D that I have to match up across the 52 weeks of start dates in column B. For purposes of this discussion I MANUALLY moved column D values to the correct row in order to match up to column B.
I don't want to MANUALLY move this each time I refresh these reports.
Also note in cell C27 I had to MANUALLY override the formula that was there before (which was F26-7) in order to account for the fact that column E changed from Wednesday to Sunday for that week.
Any thoughts or help would be greatly appreciated...I have been working on this for 2 weeks now trying to resolve.
Column B is a start date from one source while column D is data from another data source. As you can see in this example I found a total of 39 start dates in column D that I have to match up across the 52 weeks of start dates in column B. For purposes of this discussion I MANUALLY moved column D values to the correct row in order to match up to column B.
I don't want to MANUALLY move this each time I refresh these reports.
Also note in cell C27 I had to MANUALLY override the formula that was there before (which was F26-7) in order to account for the fact that column E changed from Wednesday to Sunday for that week.
Any thoughts or help would be greatly appreciated...I have been working on this for 2 weeks now trying to resolve.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D1 | =DATA!AO3 | |
| D2 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A1))),"")} | |
| D3 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A2))),"")} | |
| D4 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A3))),"")} | |
| D5 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A4))),"")} | |
| D6 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A5))),"")} | |
| D7 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A6))),"")} | |
| D8 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A7))),"")} | |
| D9 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A8))),"")} | |
| D10 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A9))),"")} | |
| D11 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A10))),"")} | |
| D12 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A11))),"")} | |
| D13 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A12))),"")} | |
| D14 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A13))),"")} | |
| D15 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A14))),"")} | |
| D16 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A15))),"")} | |
| D17 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A16))),"")} | |
| D18 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A17))),"")} | |
| D19 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A18))),"")} | |
| D20 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A19))),"")} | |
| D21 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A20))),"")} | |
| D23 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A21))),"")} | |
| D24 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A22))),"")} | |
| D25 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A23))),"")} | |
| D26 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A24))),"")} | |
| D27 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A25))),"")} | |
| D29 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A26))),"")} | |
| D30 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A27))),"")} | |
| D31 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A28))),"")} | |
| D33 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A29))),"")} | |
| D34 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A30))),"")} | |
| D35 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A31))),"")} | |
| D37 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A32))),"")} | |
| D41 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A33))),"")} | |
| D42 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A34))),"")} | |
| D44 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A35))),"")} | |
| D45 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A36))),"")} | |
| D46 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A37))),"")} | |
| D47 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A38))),"")} | |
| D48 | {=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A39))),"")} | |
| E23 | =TEXT(WEEKDAY(D23,1),"DDDD") | |
| E24 | =TEXT(WEEKDAY(D24,1),"DDDD") | |
| E25 | =TEXT(WEEKDAY(D25,1),"DDDD") | |
| E26 | =TEXT(WEEKDAY(D26,1),"DDDD") | |
| E27 | =TEXT(WEEKDAY(D27,1),"DDDD") | |
| E29 | =TEXT(WEEKDAY(D29,1),"DDDD") | |
| E30 | =TEXT(WEEKDAY(D30,1),"DDDD") | |
| E31 | =TEXT(WEEKDAY(D31,1),"DDDD") | |
| E33 | =TEXT(WEEKDAY(D33,1),"DDDD") | |
| E34 | =TEXT(WEEKDAY(D34,1),"DDDD") | |
| E35 | =TEXT(WEEKDAY(D35,1),"DDDD") | |
| E37 | =TEXT(WEEKDAY(D37,1),"DDDD") | |
| E41 | =TEXT(WEEKDAY(D41,1),"DDDD") | |
| E42 | =TEXT(WEEKDAY(D42,1),"DDDD") | |
| E44 | =TEXT(WEEKDAY(D44,1),"DDDD") | |
| E45 | =TEXT(WEEKDAY(D45,1),"DDDD") | |
| E46 | =TEXT(WEEKDAY(D46,1),"DDDD") | |
| E47 | =TEXT(WEEKDAY(D47,1),"DDDD") | |
| E48 | =TEXT(WEEKDAY(D48,1),"DDDD") | |
| E2 | =TEXT(WEEKDAY(D2,1),"DDDD") | |
| E3 | =TEXT(WEEKDAY(D3,1),"DDDD") | |
| E4 | =TEXT(WEEKDAY(D4,1),"DDDD") | |
| E5 | =TEXT(WEEKDAY(D5,1),"DDDD") | |
| E6 | =TEXT(WEEKDAY(D6,1),"DDDD") | |
| E7 | =TEXT(WEEKDAY(D7,1),"DDDD") | |
| E8 | =TEXT(WEEKDAY(D8,1),"DDDD") | |
| E9 | =TEXT(WEEKDAY(D9,1),"DDDD") | |
| E10 | =TEXT(WEEKDAY(D10,1),"DDDD") | |
| E11 | =TEXT(WEEKDAY(D11,1),"DDDD") | |
| E12 | =TEXT(WEEKDAY(D12,1),"DDDD") | |
| E13 | =TEXT(WEEKDAY(D13,1),"DDDD") | |
| E14 | =TEXT(WEEKDAY(D14,1),"DDDD") | |
| E15 | =TEXT(WEEKDAY(D15,1),"DDDD") | |
| E16 | =TEXT(WEEKDAY(D16,1),"DDDD") | |
| E17 | =TEXT(WEEKDAY(D17,1),"DDDD") | |
| E18 | =TEXT(WEEKDAY(D18,1),"DDDD") | |
| E19 | =TEXT(WEEKDAY(D19,1),"DDDD") | |
| E20 | =TEXT(WEEKDAY(D20,1),"DDDD") | |
| E21 | =TEXT(WEEKDAY(D21,1),"DDDD") | |
| B2 | =C2-6 | |
| B3 | =C3-6 | |
| B4 | =C4-6 | |
| B5 | =C5-6 | |
| B6 | =C6-6 | |
| B7 | =C7-6 | |
| B8 | =C8-6 | |
| B9 | =C9-6 | |
| B10 | =C10-6 | |
| B11 | =C11-6 | |
| B12 | =C12-6 | |
| B13 | =C13-6 | |
| B14 | =C14-6 | |
| B15 | =C15-6 | |
| B16 | =C16-6 | |
| B17 | =C17-6 | |
| B18 | =C18-6 | |
| B19 | =C19-6 | |
| B20 | =C20-6 | |
| B21 | =C21-6 | |
| B22 | =C22-6 | |
| B23 | =C23-6 | |
| B24 | =C24-6 | |
| B25 | =C25-6 | |
| B26 | =C26-6 | |
| B27 | =C27-6 | |
| B28 | =C28-6 | |
| B29 | =C29-6 | |
| B30 | =C30-6 | |
| B31 | =C31-6 | |
| B32 | =C32-6 | |
| B33 | =C33-6 | |
| B34 | =C34-6 | |
| B35 | =C35-6 | |
| B36 | =C36-6 | |
| B37 | =C37-6 | |
| B38 | =C38-6 | |
| B39 | =C39-6 | |
| B40 | =C40-6 | |
| B41 | =C41-6 | |
| B42 | =C42-6 | |
| B43 | =C43-6 | |
| B44 | =C44-6 | |
| B45 | =C45-6 | |
| B46 | =C46-6 | |
| B47 | =C47-6 | |
| B48 | =C48-6 | |
| B49 | =C49-6 | |
| B50 | =C50-6 | |
| B51 | =C51-6 | |
| B52 | =C52-6 | |
| B53 | =C53-6 | |
| C2 | =DATA!BJ3 | |
| C3 | =DATA!BJ4 | |
| C4 | =DATA!BJ5 | |
| C5 | =DATA!BJ6 | |
| C6 | =DATA!BJ7 | |
| C7 | =DATA!BJ8 | |
| C8 | =DATA!BJ9 | |
| C9 | =DATA!BJ10 | |
| C10 | =DATA!BJ11 | |
| C11 | =DATA!BJ12 | |
| C12 | =DATA!BJ13 | |
| C13 | =DATA!BJ14 | |
| C14 | =DATA!BJ15 | |
| C15 | =DATA!BJ16 | |
| C16 | =DATA!BJ17 | |
| C17 | =DATA!BJ18 | |
| C18 | =DATA!BJ19 | |
| C19 | =DATA!BJ20 | |
| C20 | =DATA!BJ21 | |
| C21 | =DATA!BJ22 | |
| C22 | =DATA!BJ23 | |
| C23 | =DATA!BJ24 | |
| C24 | =DATA!BJ25 | |
| C25 | =DATA!BJ26 | |
| C26 | =DATA!BJ27 | |
| C27 | =DATA!BJ28 | |
| C28 | =DATA!BJ29 | |
| C29 | =DATA!BJ30 | |
| C30 | =DATA!BJ31 | |
| C31 | =DATA!BJ32 | |
| C32 | =DATA!BJ33 | |
| C33 | =DATA!BJ34 | |
| C34 | =DATA!BJ35 | |
| C35 | =DATA!BJ36 | |
| C36 | =DATA!BJ37 | |
| C37 | =DATA!BJ38 | |
| C38 | =DATA!BJ39 | |
| C39 | =DATA!BJ40 | |
| C40 | =DATA!BJ41 | |
| C41 | =DATA!BJ42 | |
| C42 | =DATA!BJ43 | |
| C43 | =DATA!BJ44 | |
| C44 | =DATA!BJ45 | |
| C45 | =DATA!BJ46 | |
| C46 | =DATA!BJ47 | |
| C47 | =DATA!BJ48 | |
| C48 | =DATA!BJ49 | |
| C49 | =DATA!BJ50 | |
| C50 | =DATA!BJ51 | |
| C51 | =DATA!BJ52 | |
| C52 | =DATA!BJ53 | |
| C53 | =DATA!BJ54 | |
| F2 | =C2-Q7 | |
| F3 | =F2-7 | |
| F4 | =F3-7 | |
| F5 | =F4-7 | |
| F6 | =F5-7 | |
| F7 | =F6-7 | |
| F8 | =F7-7 | |
| F9 | =F8-7 | |
| F10 | =F9-7 | |
| F11 | =F10-7 | |
| F12 | =F11-7 | |
| F13 | =F12-7 | |
| F14 | =F13-7 | |
| F15 | =F14-7 | |
| F16 | =F15-7 | |
| F17 | =F16-7 | |
| F18 | =F17-7 | |
| F19 | =F18-7 | |
| F20 | =F19-7 | |
| F21 | =F20-7 | |
| F22 | =F21-7 | |
| F23 | =F22-7 | |
| F24 | =F23-7 | |
| F25 | =F24-7 | |
| F26 | =F25-7 | |
| F27 | =D27 | |
| F28 | =F27-7 | |
| F29 | =F28-7 | |
| F30 | =F29-7 | |
| F31 | =F30-7 | |
| F32 | =F31-7 | |
| F33 | =F32-7 | |
| F34 | =F33-7 | |
| F35 | =F34-7 | |
| F36 | =F35-7 | |
| F37 | =F36-7 | |
| F38 | =F37-7 | |
| F39 | =F38-7 | |
| F40 | =F39-7 | |
| F41 | =F40-7 | |
| F42 | =F41-7 | |
| F43 | =F42-7 | |
| F44 | =F43-7 | |
| F45 | =F44-7 | |
| F46 | =F45-7 | |
| F47 | =F46-7 | |
| F48 | =F47-7 | |
| F49 | =F48-7 | |
| F50 | =F49-7 | |
| F51 | =F50-7 | |
| F52 | =F51-7 | |
| F53 | =F52-7 | |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| DATA | =DATA!$A$3:$AI$61000 | |
