On Fri, 11 Jul 2008, in microsoft.public.excel.charting, I said:
Katherine said:
If there is only once instance of a particular business unit, then
this doesn't appear to be a problem. It's a bit manual, but generally
works. But when I need to create another row for an existing Business
Unit, then I have to manually open that Series, and add in the cell
reference to the new X, Y, and Bubble size.


I recommend laying out your data like this:

Influence/Authority
-------------------

Interest Cards & Payments Role
7 9 5
10 5 2

Interest Internet Role
10 8 2

Or, if you don't mind a few extra columns, try this. Start your Chart
wizard with the data arranged like you had it originally:

Business Unit Interest Influence/Authority Role
Cards & Payments 7 9 5
Internet 10 8 2
Cards & Payments 10 5 2

Create your bubble chart using only the last three columns, ignoring the
first column.

Now create as many new columns as there are business units, and populate
them with a formula like:

=IF($A2=E$1,C2,NA())

Leading to a block like:

Business Unit Interest I/A Role C & P Internet
C & P 7 9 5 9 #N/A
Internet 10 8 2 #N/A 2
C & P 10 5 2 5 #N/A

Now keep selecting the second to fourth columns and Paste Special them
into the chart to make new series, and drag the coloured boxes to their
new columns. The #N/A should ensure that "Internet" series has only
Internet row values, and so on.

I'm going to have to let you work out where all the series go, because
sorting it out is tricky to describe, and bubble charts are particularly
hard, but you say you've been doing this manually already, so you should
already be half way there. It's just the trick of NA() and extra columns
you needed, I think, to save extra work.

One other thing: select one or two extra blank rows at the bottom, and
always insert new blank rows when you add new data, and your chart
should always grow with your data without dynamic ranges. I do this
every day at work.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.