Combining charts from different sheets

[Dany Adams]

I have a workbook with 96 sheets, each with two columns of numbers -in
columns b and c. The numbers are information for a frequency diagram (a
histogram) where b is the datum, and c is the frequency. There are 256 rows
in each column.

I need a way to graph subsets of histograms - anywahere from 2 to 24 at a
time to be exact - on one graph. I've been doing it all by hand - moving
each pair of columns to a summary sheet then highlighting to "add data" but
now that I've got 96 of them, I really need a function or a macro or
something. Can anyone help ? Thanks

 

[Dany Adams]

Is it always the same subsets or do you sort of "choose" after
generating the histogram?

In either case, it seems to me that, after you've created a bunch of
graphs--by which I guess you mean bar-charts? --, click on the bars in
a chart. The formula bar will show something like

"=series(sheet1!$a$1,sheet1!$a$2:$a$20,...)"
change the cell references in the second argument to the rows you want.

I need to be able to do it with different subsets.

I know how to change it manually, it's just that with 96 of them, combined
in various different ways, I was hoping there was a way to automate it.

 

[Bob Greenblatt]

I need to be able to do it with different subsets.

I know how to change it manually, it's just that with 96 of them, combined
in various different ways, I was hoping there was a way to automate it.

There is a way to automate it. If you are using Excel 2008, the choices are
AppleScript or XLM. If Excel 2004 in addition to these, you can use VBA.
But, in order to automate it, you will have to provide some detailed
specifics so that we can help. If you are not willing, or able to do this
yourself, there are people who will do it for a fee from well defined
specifications.

 

[roblake]

Bob mentioned:
"There is a way to automate it. If you are using Excel 2008, the choices are
AppleScript or XLM. If Excel 2004 in addition to these, you can use VBA."

I'm sitting here, looking at two shelves of Excel books and manuals, dating back a decade or so. Please, which versions of Excel, Mac or PC, support XLM? And how do I access that capability?

I have not found "XLM" mentioned in the indices that I've looked at. I run both OS X and Windows XP Pro.

 

[Dany Adams]

There is a way to automate it. If you are using Excel 2008, the choices are
AppleScript or XLM. If Excel 2004 in addition to these, you can use VBA.
But, in order to automate it, you will have to provide some detailed
specifics so that we can help. If you are not willing, or able to do this
yourself, there are people who will do it for a fee from well defined
specifications.

I'm using Excel 2004 (for Mac obviously) version 11.3.5 (070411)

I've actually just discovered one shortcut - if you copy one graph then
paste it onto another, they combine. A big advance, but I would still like
to be able to automate.

What specifics do you need ?

 

[JE McGimpsey]

I'm sitting here, looking at two shelves of Excel books and manuals, dating
back a decade or so. Please, which versions of Excel, Mac or PC, support XLM?
And how do I access that capability?

These version of XL support XLM:

MacXL4, 5, 98, 01, vX, 04, 08
WinXL4, 5, 95, 97, 00, 02, 03, 07

See

http://tinyurl.com/4342nk

 

[Carl Witthoft]

I need to be able to do it with different subsets.

I know how to change it manually, it's just that with 96 of them, combined
in various different ways, I was hoping there was a way to automate it.

It can be done, I think, without using macros. I have successfully used
the OFFSET function combined with Names (for ranges of cells) to let me
type a cell address or two into a couple cells, and let the OFFSET func,
combined with chart series definitions that look sort of like

SERIES([label],xarray, yarray) where xarray and yarray are defined
Names which use OFFSET to identify the ranges. Here's an example:

Xarray=OFFSET(ScatterPlot!$B$4,0,0,nS,1)
nS=COUNT(ScatterPlot!$B:$B)

which probably doesn't make things any clearer :-(

but you can also define a name like Xdata=sheet1!A1
and let cell A1 have a formula like =concatenate("sheet1!",b1,":",c1)
so that by changing the contents of b1 and c1, cell A1 has the string
needed to define the chart series.

HTH :-(

Carl