Referencing Chart Data Based on Cell Value

Hydestone

Board Regular
Joined
Mar 29, 2010
Messages
124
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
ABCDE
1project info
2nameTest Job
3number12345
4
5project start1/1/2020
6project finish12/15/2020
7pay periods12
8
9contract total 2,500,000 numbers are good
10retainage (%)5.00%
11reduce at 50%?YY or N
12optionFC, F, B, or T
13
14
15optionscustomfront loadbelltail load
16periodCFBT
1710%6%2%2%
1820%10%4%4%
1930%16%8%6%
2040%18%10%8%
2150%16%12%10%
2260%10%14%12%
2370%8%14%14%
2480%6%12%16%
2590%4%10%14%
26100%3%8%8%
27110%2%4%4%
28120%1%2%2%
29total0%100%100%100%
30% off by 100%0%0%0%
draw schedule
Cell Formulas
RangeFormula
C7C7=DATEDIF(EOMONTH(C5,-1)+1,EOMONTH(C6,0),"m")+1
D9D9=IF((contract<>M17),"numbers are off!","numbers are good")
A18:A28A18=IF((AND(A17<$C$7,A17<>0)),A17+1,0)
B29:E29B29=SUM(B17:B28)
B30:E30B30=1-B29
Named Ranges
NameRefers ToCells
contract='draw schedule'!$C$9D9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A17:A28Cell Value<1textNO



Anticipated Draw Schedule - Copy.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2periodsFBTperiodsFBTperiodsFBTperiodsFBTperiodsFBT
31100%100%100%175%50%25%140%25%20%130%15%10%115%10%5%
4100%100%100%225%50%75%240%50%40%235%35%25%235%25%15%
5100%100%100%320%25%40%325%35%35%330%30%30%
6100%100%100%410%15%30%415%25%35%
7100%100%100%55%10%15%
8100%100%100%
9
10periodsFBTperiodsFBTperiodsFBTperiodsFBTperiodsFBT
11110%5%5%110%5%4%110%5%2%18%4%2%16%3%1%
12220%15%10%215%10%6%215%10%4%212%8%4%212%7%3%
13335%30%20%325%20%10%320%15%12%318%14%6%316%11%6%
14420%30%35%430%30%30%422%20%15%422%18%10%420%13%8%
15510%15%20%510%20%25%515%20%22%518%18%18%516%16%12%
1665%5%10%66%10%15%612%15%20%610%14%22%612%16%16%
17100%100%100%74%5%10%74%10%15%76%12%18%78%13%20%
18100%100%100%82%5%10%84%8%12%86%11%16%
19100%100%100%92%4%8%93%7%12%
20100%100%100%101%3%6%
21100%100%100%
22
23periodsFBTperiodsFBT
2416%2%1%15%1%1%
2529%6%2%28%5%2%
26313%9%4%312%8%4%
27414%11%6%414%10%6%
28516%13%8%516%12%8%
29621%18%21%614%14%10%
3078%13%16%710%14%14%
3186%11%14%88%12%16%
3294%9%13%96%10%14%
33102%6%9%104%8%12%
34111%2%6%112%5%8%
35100%100%100%121%1%5%
36100%100%100%
curve data
Cell Formulas
RangeFormula
B4:D4B4=SUM(B3)
G5:I5G5=SUM(G3:G4)
L6:N6L6=SUM(L3:L5)
Q7:S7Q7=SUM(Q3:Q6)
V8:X8V8=SUM(V3:V7)
B17:D17B17=SUM(B11:B16)
G18:I18G18=SUM(G11:G17)
L19:N19L19=SUM(L11:L18)
Q20:S20Q20=SUM(Q11:Q19)
V21:X21V21=SUM(V11:V20)
B35:D35B35=SUM(B24:B34)
G36:I36G36=SUM(G24:G35)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,603
Members
416,928
Latest member
ezmeng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top