Dynamic chart format & data selection?

[jg70124]

I have 3 columns of data: "Type", "Sales", "GM%".

I would like to create a series of scatters (one for each value of
"Type") with "Sales" on the x axis and GM% on the y axis. In the past
when I've had to do this, I've sorted by "type" then manually selected
the appropriate cells. But this time I have 40k rows with 35
different values of "Type". Furthermore, each chart will have two
different series ("Types") that will be differentiated by color.
Doing it by hand will take hours and hours - and the data is likely to
change later.

So is there a way to programatically select and color data series in
Excel 2007?

 

[Jon Peltier]

How about formulaically (without VBA)?

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon

 

[jg70124]

8^)

Should have put in my first post: "Already checked peltiertech.com"...

I have used that method in the past. In this case, with 35 different
types, that's a lot of helper columns - doesn't really save much time.

 

[Jon Peltier]

Except that the formulas automatically adjust if the input data changes.

- Jon

 

[jg70124]

Of course you are correct.

Out of curiosity, is there a way to embed conditional formulae in the
chart's data definition - or some other way to do it without helper
columns?

Thanks

 

[Jon Peltier]

You could probably construct Names that have clever Refers To formulas, as
in dynamic charts:

http://peltiertech.com/Excel/Charts/Dynamics.html

but I usually find that the more clever your formula is, the harder it is to
debug when it's misbehaving. A couple dozen helper columns are easier to
work with and maintain than a half dozen Name definitions.

- Jon