Dynamic Bubble Chart - Legend & Data Labelling problem

[Jaspal]

Hello all,

In my Dynamic Bubble chart, I have used offset formula in Named ranges for relevant criteria, but my Legends don't pick up individual products, I have switch legend on, its just dumped as one. Same problems with data labels. I need to pick up Product names for each Bubble without having to individual pick it up.

Would appreciate a Macro and a formula based solution. I have copied the spreadsheet below, could not find a button to upload the spreadsheet.

[TABLE="width: 1091"]
<TBODY>[TR]
[TD]OFFSET(Test1!$C$5,,1,COUNT(Test1!$D$5:$D$27))</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eg of my formula, used in my name range</SPAN>[/TD]
[TD]Product</SPAN>[/TD]
[TD]Revenue CAGR </SPAN>[/TD]
[TD]Ave Contrib Margin</SPAN>[/TD]
[TD]Revenue 2012B</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A</SPAN>[/TD]
[TD]8%</SPAN>[/TD]
[TD]74%</SPAN>[/TD]
[TD]28,619.2 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B</SPAN>[/TD]
[TD]9%</SPAN>[/TD]
[TD]65%</SPAN>[/TD]
[TD]7,691.5 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C</SPAN>[/TD]
[TD]17%</SPAN>[/TD]
[TD]61%</SPAN>[/TD]
[TD]9,685.5 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D</SPAN>[/TD]
[TD]23%</SPAN>[/TD]
[TD]52%</SPAN>[/TD]
[TD]2,347.3 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E</SPAN>[/TD]
[TD]3%</SPAN>[/TD]
[TD]41%</SPAN>[/TD]
[TD]2,632.6 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]F</SPAN>[/TD]
[TD]11%</SPAN>[/TD]
[TD]70%</SPAN>[/TD]
[TD]3,366.7 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]G</SPAN>[/TD]
[TD]17%</SPAN>[/TD]
[TD]57%</SPAN>[/TD]
[TD]2,056.5 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]H</SPAN>[/TD]
[TD]39%</SPAN>[/TD]
[TD]17%</SPAN>[/TD]
[TD]420.2 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I</SPAN>[/TD]
[TD]47%</SPAN>[/TD]
[TD]54%</SPAN>[/TD]
[TD]478.4 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J</SPAN>[/TD]
[TD]7%</SPAN>[/TD]
[TD]43%</SPAN>[/TD]
[TD]1,201.1 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]K</SPAN>[/TD]
[TD]26%</SPAN>[/TD]
[TD]37%</SPAN>[/TD]
[TD]664.89 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]L</SPAN>[/TD]
[TD]72%</SPAN>[/TD]
[TD]32%</SPAN>[/TD]
[TD]186.84 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M</SPAN>[/TD]
[TD]22%</SPAN>[/TD]
[TD]68%</SPAN>[/TD]
[TD]1,024.28 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]N</SPAN>[/TD]
[TD]21%</SPAN>[/TD]
[TD]27%</SPAN>[/TD]
[TD]265.00 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]

Bitmap </SPAN>

<TBODY>
</TBODY>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]