Question: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
1) How (should I) would I use the OFFSET function to create a dynamic named range that will allow me to pull ONLY specific data?<o></o>
2) Is this the best way to do this without using VBA?<o></o>
I have some very large data sets that I need to pull/update information into a chart. In an ideal world, I would have the data pulled automatically as I calculate the raw information so a dynamic named range. Here are two screen shots that give you an idea of how raw data is set up. The potential for these data sets to reach over 1000 lines is highly probable. <o></o>
<o> </o>
Currently, I manually place each rate from the DATA sheet in the respective YEAR column for the CHART data. <o></o>
<o> </o>
The CHART will need to plot the RATE relative to the YEARS. That means there could be many different RATES in the same YEAR. These ‘Series’ are all re-formatted to look like the same series without any lines connecting the data points. <o></o>
<o> </o>
Data<o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o></o>
<o></o>
Chart & Chart Data<o></o>
<o></o>
<o>
</o>
<o></o>
If there is a better way to achieve this outside of the named range, let me know!<o></o>
<o> </o>
fficeffice" /><o></o>1) How (should I) would I use the OFFSET function to create a dynamic named range that will allow me to pull ONLY specific data?<o
></o>2) Is this the best way to do this without using VBA?<o
></o>I have some very large data sets that I need to pull/update information into a chart. In an ideal world, I would have the data pulled automatically as I calculate the raw information so a dynamic named range. Here are two screen shots that give you an idea of how raw data is set up. The potential for these data sets to reach over 1000 lines is highly probable. <o
></o><o
> </o>Currently, I manually place each rate from the DATA sheet in the respective YEAR column for the CHART data. <o
></o><o
> </o>The CHART will need to plot the RATE relative to the YEARS. That means there could be many different RATES in the same YEAR. These ‘Series’ are all re-formatted to look like the same series without any lines connecting the data points. <o
></o><o
> </o>Data<o
></o><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o
referrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o></o>
></o>Chart & Chart Data<o
></o><o
></o><o
>
><o
></o>If there is a better way to achieve this outside of the named range, let me know!<o
></o><o
> </o>
