Offset formula not working for Labels and Legends in a graph - Please help

[Jaspal]

Hello there,</SPAN>

I have tried to create a Dynamic Bubble graph, by using Offset function formula in name range to populate relevant criteria for the Bubble graph. It does update the Bubble’s but does not put individual product against the respective Bubbles, i.e. A against a Bubble, B against other, but dumps all the Products against each Bubble. Also, in Legend I want the name of the products to appear.</SPAN>

Could you please suggest a Macro and formula based solution as well, as I would have to circulate this spreadsheet to a Wider group.</SPAN>

Many thanks in advance,


</SPAN>[TABLE="width: 1461"]
<TBODY>[TR]
[TD]OFFSET(Test1!$C$5,,,COUNT(Test1!$F$5:$F$27))</SPAN>[/TD]
[TD]For Series name</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OFFSET(Test1!$C$5,,1,COUNT(Test1!$D$5:$D$27))</SPAN>[/TD]
[TD]For X series</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]OFFSET(Test1!$D$5,,1,COUNT(Test1!$E$5:$E$27))</SPAN>[/TD]
[TD]For Y series</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OFFSET(Test1!$E$5,,1,COUNT(Test1!$F$5:$F$27))</SPAN>[/TD]
[TD]For Bubble size</SPAN>[/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][/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][/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][/TD]
[TD]D</SPAN>[/TD]
[TD]23%</SPAN>[/TD]
[TD]52%</SPAN>[/TD]
[TD]2,347.3 </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]

Bitmap </SPAN>

<TBODY>
</TBODY>

[/TD]
[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][/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][/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][/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][/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][/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][/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][/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][/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][/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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[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]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]