Overlay charts

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

I am looking to produce overlay charts and would like
confirmation of my own understanding of this topic.
Previous posts have yielded some very helpful results
(thanks Jon Peltier). Am I right in thinking that to
produce overlay charts it is something that I have to code
into the chart rather than it being a standard Excel menu
option? From the previous replies I have got example code
and I can handle that but I just wanted to make sure that
was the way to go??

Thanks for your comments,
Ian
 
Ian -

Do you mean you want to make a combination chart? The built in
combination charts are not all-inclusive, but Excel allows you to
construct your own combinations.

Make a chart with all series of the same type, then change some series
by selecting each in turn and choosing Chart Type from the Chart menu.

Overlaying charts is a complicated process and usually unnecessary.

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

Thanks for your reply, I'm a little confused now as to what I need. What I'm
doing is this, I sample chemicals in a spectrophotometer and the data
(Absorbance against Wavelength) are recorded in Excel. I then produce a graph
of this data. I then want to be able to overlay a separate graph of a
different chemicals' "signature trace" on top of this so I can get a visual
display of the two chemicals. The axes would be the same in both cases. Do
you think this is an Overlay or Combination chart? Thanks for your comments.

Ian.
 
Ian -

This is even easier (when you know how)! It's a simple two series
scatter chart. Start with the XY Scatter chart of the new data. Open the
workbook with the comparison data. Select and copy the X and Y data,
then select the chart, and use Paste Special from the Edit menu to add
the data as a new series.

Alternatively, with the second workbook open, select the chart, go to
Source Data on the Chart menu, select the Series tab, click Add, and
navigate to the other workbook and select the X and Y. The Windows menu
works with this dialog open to allow you to switch windows.

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

You're right it is easy when you know how. The comparitive chemical data is
being held in a separate Excel workbook (Library.xls) and I managed to create
a macro to pull that data in and add it as a second series to the original
chart as you suggest. It does run a little "rough" in that you can see stuff
flashing by in the background. Can this be improred upon?

Also as I expand the library of comparative data it would be nice to have a
drop-down box that offers all the possibilities in the library, maybe as a
list of the macros produced to pull in the different chemical data. Can you
point me to some help for setting up such a drop down box (combobox or list)
as the Excel help files have not really explained too much.

Thanks again,
Ian.
 
Hi Ian -

To prevent the screen flashing, use this approach:

Application.ScreenUpdating = False
' your code goes here
Application.ScreenUpdating = True

In addition to this, you can smooth things out by replacing

Object.Select
Selection.Method

with this

Object.Method

The selection of objects is almost always unnecessary, and it takes time and causes
the screen to need frequent redrawing.

Do you want an in-sheet listbox, or are you building a userform? These use different
types of list boxes. You could make use of my favorite resource, and enter "excel
worksheet listbox" into an advanced Google search.

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

Great advice, thanks. The screen flashing has gone. As far as the listbox
issue goes, I hadn't thought about building a userform but will follow the
links you suggest and decide which way to go.

Thanks again for all your help.

Ian.
 
Ian -

I imagine that a Forms menu listbox in the worksheet is what you want. A toolbox on
a userform is more versatile, but takes more to set up, particularly if you are
relatively inexperienced.

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

Oh dear is my inexperience that obvious? Access is more my thing but I'm
trying to get familiar with Excel.

Having read around forms and list boxes I'm still a bit unsure about whether
a list/combobox will do what I want. I have this file, Library.xls with a
number of different worksheets. Each worksheet has data on different
chemicals. In the test document I want a list/combobox that when clicked
gives me a list of the worksheets in Library.xls and then brings that data in
to overlay as we've previoulsy done. I guess that selecting the chemical in
the list/combobox will run a macro that gets that data etc...it's just
popluating the list with the names of the Library.xls worksheets. What do you
think?

I really appreciate your help,
Ian.
 
Ian -

I have made a sample file and placed it on my web site. The file has five sheets:
Test, Hydrogen, Helium, Lithium, and Beryllium. All sheets have data in A1:B21. The
sheet named Test also has other features. It has a chart with the Test data and one
other series. There are two Forms Toolbar controls, a List Box and a Combo Box, both
using the same Input Range (i.e., names of the other sheets) and Cell Link (which
list element is selected). There is a range of data in P1:Q21 which is linked to one
of the reference sheets using the INDIRECT worksheet function and the selected list
element.

It's not too heavily documented <g>, but it follows a couple pages on my site:

http://peltiertech.com/Excel/Charts/ChartByControl.html
http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

The workbook is contained in this zip file:

http://peltiertech.com/Excel/Zips/ChartAgainstStandard.zip

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

Neat, very neat! That's just what I'm looking for and not a line of code in
sight. I followed through your explanations on the pages of your site and was
able to follow your thinking perfectly. I guess that if you want to point to
a worksheet in a different Excel file you just need to construct the INDIRECT
function to reflect it's path? Just out of interest, do you know how many
worksheets you can have in a workbook?

Your help to me has been most appreciated, thank you very much indeed.

Ian.
 
Ian said:
Hi Jon,

Neat, very neat! That's just what I'm looking for and not a line of code in
sight. I followed through your explanations on the pages of your site and was
able to follow your thinking perfectly.
Great!

I guess that if you want to point to
a worksheet in a different Excel file you just need to construct the INDIRECT
function to reflect it's path?
Click to expand...

Correct. It's possible to reference cells in a closed workbook, but I
have a nagging suspicion the address has to be hard coded, not
constructed using INDIRECT. A quick test will show you the answer. If
you keep the files open, no big deal.
Just out of interest, do you know how many
worksheets you can have in a workbook?
Click to expand...

Limited by available memory, which doesn't mean how many gigs of ram you
have, but rather how much of it Excel is designed to use (relatively
little by today's standards).

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