Dynamic chart format & data selection?
 

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?

Dynamic chart format & data selection?
 

How about formulaically (without VBA)?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
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?

Dynamic chart format & data selection?
 

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.




On Feb 15, 1:00 pm, "Jon Peltier"
wrote:
How about formulaically (without VBA)?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

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?

Dynamic chart format & data selection?
 

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
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.




On Feb 15, 1:00 pm, "Jon Peltier"
wrote:
How about formulaically (without VBA)?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

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?

Dynamic chart format & data selection?
 

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

On Feb 15, 4:00 pm, "Jon Peltier"
wrote:
Except that the formulas automatically adjust if the input data changes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

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.

On Feb 15, 1:00 pm, "Jon Peltier"
wrote:
How about formulaically (without VBA)?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

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?

Dynamic chart format & data selection?
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
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

On Feb 15, 4:00 pm, "Jon Peltier"
wrote:
Except that the formulas automatically adjust if the input data changes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

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.

On Feb 15, 1:00 pm, "Jon Peltier"
wrote:
How about formulaically (without VBA)?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...

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?