Lookup a row to populate a chart

[sdoppke]

Hi everyone, this seems to be seldom covered... I am lookin to create a chart from a spread sheet that has thousands of rows and hundreds of columns.

The idea would be to use a value in a cell on another worksheet in another workbook to lookup an array of data (all on the same row)

<TABLE style="WIDTH: 783pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1044><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" span=29 width=36><TBODY><TR style="HEIGHT: 10.5pt; mso-height-source: userset" height=14><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 27pt; HEIGHT: 10.5pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl67 height=14 width=36>A</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl68 width=36>B</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl69 width=36> </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=36>C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=72 colSpan=2>D</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl73 width=72 colSpan=2>E</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=72 colSpan=2>F</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl73 width=72 colSpan=2>G</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=72 colSpan=2>H</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=72 colSpan=2>I</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=72 colSpan=2>J</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl73 width=72 colSpan=2>K</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=36>L</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=72 colSpan=2>M</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl66 width=36>N</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=36>N</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=72 colSpan=2>O</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl75 width=72 colSpan=2>P</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 783pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1044><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" span=29 width=36><TBODY><TR style="HEIGHT: 10.5pt; mso-height-source: userset" height=14><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 27pt; HEIGHT: 10.5pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl65 height=14 width=36>1589</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl66 width=36>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl67 width=36> </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=36>0%</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl74 width=72 colSpan=2>3</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl71 width=72 colSpan=2>$69,350,268</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=72 colSpan=2>$9,701,995</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl71 width=72 colSpan=2>$23,574,651</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=72 colSpan=2>$231,716</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=72 colSpan=2>$38,338,500</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=72 colSpan=2>$0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl71 width=72 colSpan=2>$7,146,080</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=36>$21.49</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=72 colSpan=2>$0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=36>65%</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=36>0%</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=72 colSpan=2>$9,933,710</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl73 width=72 colSpan=2>25,105</TD></TR></TBODY></TABLE>

The chart would find A1 (a value in a cell on another worksheet in another workbook) then use D1-H1 to populate on line in the chart and J1-N-1 to populate another line in the same chart. etc..

Would anyone be able to help walk me through the set up of a chart like this?

Thanks a ton in advacne

sd

 

[Steve=True]

You can make a dynamic chart by using a named range for your series where the named range is an offset formula for the data you have chosen.

To choose the data, you would probably use index and match combination formulas to find the right row.

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

 

[sdoppke]

You can make a dynamic chart by using a named range for your series where the named range is an offset formula for the data you have chosen.

To choose the data, you would probably use index and match combination formulas to find the right row.

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

Thanks Steve, sounds complicated, but Ill this a go.