Can't get data pulled into chart easily

[Wanabe]

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. Here are two screen shots that give you an idea of how I set this information up. The potential for these data sets to reach over 1000 lines is highly probable.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Currently, I manually place each rate from the DATA sheet in the respective YEAR column for the CHART data.
<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>
What formulas, vlookups, etc would I need to have in the CHART data to automatically sort and position the data so that I’m not manually keying this information in
<o></o>
Data
<o>

</o>

Chart & Chart Data
<o>

</o>
<o></o>

This all sounded very clear and understandable in my head…not so sure now that I have it typed out.

 

[GlennUK]

An array formula ( entered with Ctrl-Shift-Enter instead of Enter ) would do it, like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Rate	Years	*	Years	0	1	2
2	Renee Z	12.38	0	*	Rates	12.38	12.82	13.25
3	Tom	14.38	0	*	*	14.38	*	*
4	Jones	15.19	0	*	*	15.19	*	*
5	Mike Scott	12.82	1	*	*	*	*	*
Sheet17

... entered into cell F2, and then copied as required.

 

[Wanabe]

Column F is the only one with problems. The results from F2 on down is a #NA and it looks like this has something to do with the way the value in F1 is in the cell. On my real workbook, the value in F1 is 0.0 as a number, but when I change it to any integer greater than zero, it works fine.

Great solution if I can work that out!!!