I am trying to populate a chart located in cells C17:E28 based the value in cell C7. The chart data is contained on a separate sheet 'curve data'. Any recommendations on how I might accomplish this? Perhaps one of the lookup functions?
| Anticipated Draw Schedule - Copy.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | project info | ||||||
| 2 | name | Test Job | |||||
| 3 | number | 12345 | |||||
| 4 | |||||||
| 5 | project start | 1/1/2020 | |||||
| 6 | project finish | 12/15/2020 | |||||
| 7 | pay periods | 12 | |||||
| 8 | |||||||
| 9 | contract total | 2,500,000 | numbers are good | ||||
| 10 | retainage (%) | 5.00% | |||||
| 11 | reduce at 50%? | Y | Y or N | ||||
| 12 | option | F | C, F, B, or T | ||||
| 13 | |||||||
| 14 | |||||||
| 15 | options | custom | front load | bell | tail load | ||
| 16 | period | C | F | B | T | ||
| 17 | 1 | 0% | 6% | 2% | 2% | ||
| 18 | 2 | 0% | 10% | 4% | 4% | ||
| 19 | 3 | 0% | 16% | 8% | 6% | ||
| 20 | 4 | 0% | 18% | 10% | 8% | ||
| 21 | 5 | 0% | 16% | 12% | 10% | ||
| 22 | 6 | 0% | 10% | 14% | 12% | ||
| 23 | 7 | 0% | 8% | 14% | 14% | ||
| 24 | 8 | 0% | 6% | 12% | 16% | ||
| 25 | 9 | 0% | 4% | 10% | 14% | ||
| 26 | 10 | 0% | 3% | 8% | 8% | ||
| 27 | 11 | 0% | 2% | 4% | 4% | ||
| 28 | 12 | 0% | 1% | 2% | 2% | ||
| 29 | total | 0% | 100% | 100% | 100% | ||
| 30 | % off by | 100% | 0% | 0% | 0% | ||
draw schedule | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C7 | C7 | =DATEDIF(EOMONTH(C5,-1)+1,EOMONTH(C6,0),"m")+1 |
| D9 | D9 | =IF((contract<>M17),"numbers are off!","numbers are good") |
| A18:A28 | A18 | =IF((AND(A17<$C$7,A17<>0)),A17+1,0) |
| B29:E29 | B29 | =SUM(B17:B28) |
| B30:E30 | B30 | =1-B29 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| contract | ='draw schedule'!$C$9 | D9 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A17:A28 | Cell Value | <1 | text | NO |
| Anticipated Draw Schedule - Copy.xlsx | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | ||||||||||||||||||||||||||
| 2 | periods | F | B | T | periods | F | B | T | periods | F | B | T | periods | F | B | T | periods | F | B | T | ||||||
| 3 | 1 | 100% | 100% | 100% | 1 | 75% | 50% | 25% | 1 | 40% | 25% | 20% | 1 | 30% | 15% | 10% | 1 | 15% | 10% | 5% | ||||||
| 4 | 100% | 100% | 100% | 2 | 25% | 50% | 75% | 2 | 40% | 50% | 40% | 2 | 35% | 35% | 25% | 2 | 35% | 25% | 15% | |||||||
| 5 | 100% | 100% | 100% | 3 | 20% | 25% | 40% | 3 | 25% | 35% | 35% | 3 | 30% | 30% | 30% | |||||||||||
| 6 | 100% | 100% | 100% | 4 | 10% | 15% | 30% | 4 | 15% | 25% | 35% | |||||||||||||||
| 7 | 100% | 100% | 100% | 5 | 5% | 10% | 15% | |||||||||||||||||||
| 8 | 100% | 100% | 100% | |||||||||||||||||||||||
| 9 | ||||||||||||||||||||||||||
| 10 | periods | F | B | T | periods | F | B | T | periods | F | B | T | periods | F | B | T | periods | F | B | T | ||||||
| 11 | 1 | 10% | 5% | 5% | 1 | 10% | 5% | 4% | 1 | 10% | 5% | 2% | 1 | 8% | 4% | 2% | 1 | 6% | 3% | 1% | ||||||
| 12 | 2 | 20% | 15% | 10% | 2 | 15% | 10% | 6% | 2 | 15% | 10% | 4% | 2 | 12% | 8% | 4% | 2 | 12% | 7% | 3% | ||||||
| 13 | 3 | 35% | 30% | 20% | 3 | 25% | 20% | 10% | 3 | 20% | 15% | 12% | 3 | 18% | 14% | 6% | 3 | 16% | 11% | 6% | ||||||
| 14 | 4 | 20% | 30% | 35% | 4 | 30% | 30% | 30% | 4 | 22% | 20% | 15% | 4 | 22% | 18% | 10% | 4 | 20% | 13% | 8% | ||||||
| 15 | 5 | 10% | 15% | 20% | 5 | 10% | 20% | 25% | 5 | 15% | 20% | 22% | 5 | 18% | 18% | 18% | 5 | 16% | 16% | 12% | ||||||
| 16 | 6 | 5% | 5% | 10% | 6 | 6% | 10% | 15% | 6 | 12% | 15% | 20% | 6 | 10% | 14% | 22% | 6 | 12% | 16% | 16% | ||||||
| 17 | 100% | 100% | 100% | 7 | 4% | 5% | 10% | 7 | 4% | 10% | 15% | 7 | 6% | 12% | 18% | 7 | 8% | 13% | 20% | |||||||
| 18 | 100% | 100% | 100% | 8 | 2% | 5% | 10% | 8 | 4% | 8% | 12% | 8 | 6% | 11% | 16% | |||||||||||
| 19 | 100% | 100% | 100% | 9 | 2% | 4% | 8% | 9 | 3% | 7% | 12% | |||||||||||||||
| 20 | 100% | 100% | 100% | 10 | 1% | 3% | 6% | |||||||||||||||||||
| 21 | 100% | 100% | 100% | |||||||||||||||||||||||
| 22 | ||||||||||||||||||||||||||
| 23 | periods | F | B | T | periods | F | B | T | ||||||||||||||||||
| 24 | 1 | 6% | 2% | 1% | 1 | 5% | 1% | 1% | ||||||||||||||||||
| 25 | 2 | 9% | 6% | 2% | 2 | 8% | 5% | 2% | ||||||||||||||||||
| 26 | 3 | 13% | 9% | 4% | 3 | 12% | 8% | 4% | ||||||||||||||||||
| 27 | 4 | 14% | 11% | 6% | 4 | 14% | 10% | 6% | ||||||||||||||||||
| 28 | 5 | 16% | 13% | 8% | 5 | 16% | 12% | 8% | ||||||||||||||||||
| 29 | 6 | 21% | 18% | 21% | 6 | 14% | 14% | 10% | ||||||||||||||||||
| 30 | 7 | 8% | 13% | 16% | 7 | 10% | 14% | 14% | ||||||||||||||||||
| 31 | 8 | 6% | 11% | 14% | 8 | 8% | 12% | 16% | ||||||||||||||||||
| 32 | 9 | 4% | 9% | 13% | 9 | 6% | 10% | 14% | ||||||||||||||||||
| 33 | 10 | 2% | 6% | 9% | 10 | 4% | 8% | 12% | ||||||||||||||||||
| 34 | 11 | 1% | 2% | 6% | 11 | 2% | 5% | 8% | ||||||||||||||||||
| 35 | 100% | 100% | 100% | 12 | 1% | 1% | 5% | |||||||||||||||||||
| 36 | 100% | 100% | 100% | |||||||||||||||||||||||
curve data | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:D4 | B4 | =SUM(B3) |
| G5:I5 | G5 | =SUM(G3:G4) |
| L6:N6 | L6 | =SUM(L3:L5) |
| Q7:S7 | Q7 | =SUM(Q3:Q6) |
| V8:X8 | V8 | =SUM(V3:V7) |
| B17:D17 | B17 | =SUM(B11:B16) |
| G18:I18 | G18 | =SUM(G11:G17) |
| L19:N19 | L19 | =SUM(L11:L18) |
| Q20:S20 | Q20 | =SUM(Q11:Q19) |
| V21:X21 | V21 | =SUM(V11:V20) |
| B35:D35 | B35 | =SUM(B24:B34) |
| G36:I36 | G36 | =SUM(G24:G35) |
