Interactive XY Scatter chart

[K. Georgiadis]

I have created an interactive XY scatter chart, driven by
a drop down box, whereby the use of the INDEX function
determines which of the 15 different dimensions to plot.
My challenge is that the numbers of these 15 dimensions
come in three different formats (general, currency and
percent.) Any ideas how I might have the Y axis of the
chart change according to the number format of the chosen
dimension?

The dimensions look something like this:

Price $1.50 $1.80 $1.40 $1.85 $1.40
Years to maturity 3 4 3 5 4
Probability 80% 77% 85% 80% 75%
etc.

 

[Tushar Mehta]

I just tested this with XL2003 and the program adjusts the y axis
format to match the format of the plotted data. So, I am not sure what
the problem is.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[K. Georgiadis]

I'm using Excel 2002, which uses a General format for the
Y axis, even if the data to be plotted is in Currency or
Percent format.

 

[Guest]

A further thought: my problem may lie in the fact that
the chart does not pull the numbers directly from the
data table; I transfer a single row of data (which I
labelled "data being displayed") to a different part of
the worksheet, driven by the INDEX formula and the combo
box. The chart pulls its data from this new row. I
probably would be fine if I had a provision to change the
format of the row "data being displayed" to match the
format of the data array it is drawing from.

 

[K. Georgiadis]

Newsflash! I manually changed the format of the row "data
being displayed" and it is still not being picked up by
the chart!

 

[Tushar Mehta]

Don't know what to write. Works for me -- with XL2002.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Yes, your problem lies in that you are 'buffering' the chart from your
actual data with an intermediate range. Instead, use a named formula
for the chart that indexes the original data and it will work just
fine.

Suppose your sample data are in A1:F3. Suppose the index indicating
which row to plot is in J6. Then, create a named formula (Insert |
Name > Define...) such as
PlotData =OFFSET(Sheet1!$B$1,Sheet1!$J$6-1,0,1,COUNTA(Sheet1!$1:$1)-1)
Now, use PlotData as the source of the chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[K. Georgiadis]

thanks for the help. The formula lets Excel pick up the
right Y Axis format depending on the data being plotted,
however I have not been able to solve my problem entirely
(obviously due to lack of skill!): the formula statically
plotted the first data array but I don't know how to
enable the user to select which row to plot. Previously,
the selection was being driven by a combo box but, as you
remarked, the creation of a buffered intermediate range
made it impossible for Excel to adapt the format. Am I
trying to do something which is impossible?

 

[Tushar Mehta]

You can use a drop down box in conjunction with the technique shown in
the previous post.

If you are using the combo box from the Forms toolbar it directly gives
you the desired index. Set the Cell Link to J6.

If you are using the combo box from the Control Toolbox toolbar, it
gives you the actual value and not the index. Set the LinkedCell
property of the combo box to some cell, say J5. Then, in J6 use the
formula =MATCH(J5,A1:A3,0)

For more see the Excel/Tutorials/Dynamic Charts page of my web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Thanks! I will definitely visit your website for a
thorough tutorial on the topic of dynamic charts. I
visited your website a few times before and found it very
powerful.

-----Original Message-----
You can use a drop down box in conjunction with the technique shown in
the previous post.

If you are using the combo box from the Forms toolbar it directly gives
you the desired index. Set the Cell Link to J6.

If you are using the combo box from the Control Toolbox toolbar, it
gives you the actual value and not the index. Set the LinkedCell
property of the combo box to some cell, say J5. Then, in J6 use the
formula =MATCH(J5,A1:A3,0)

For more see the Excel/Tutorials/Dynamic Charts page of my web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

thanks for the help. The formula lets Excel pick up the
right Y Axis format depending on the data being plotted,
however I have not been able to solve my problem entirely
(obviously due to lack of skill!): the formula statically
plotted the first data array but I don't know how to
enable the user to select which row to plot. Previously,
the selection was being driven by a combo box but, as you
remarked, the creation of a buffered intermediate range
made it impossible for Excel to adapt the format. Am I
trying to do something which is impossible?

-----Original Message-----
Yes, your problem lies in that you are 'buffering'

the
chart from your

actual data with an intermediate range. Instead, use

a
named formula

for the chart that indexes the original data and it

will
work just

fine.

Suppose your sample data are in A1:F3. Suppose the index indicating
which row to plot is in J6. Then, create a named formula (Insert |
Name > Define...) such as
PlotData =OFFSET(Sheet1!$B$1,Sheet1!$J$6-1,0,1,COUNTA (Sheet1!$1:$1)-1)
Now, use PlotData as the source of the chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

A further thought: my problem may lie in the fact that
the chart does not pull the numbers directly from the
data table; I transfer a single row of data (which I
labelled "data being displayed") to a different

part
of

the worksheet, driven by the INDEX formula and the combo
box. The chart pulls its data from this new row. I
probably would be fine if I had a provision to

change
the

format of the row "data being displayed" to match the
format of the data array it is drawing from.
-----Original Message-----
I just tested this with XL2003 and the program adjusts
the y axis
format to match the format of the plotted data.

So,
I

am not sure what
the problem is.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions