 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / May 2008Combining charts from different sheets
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 > 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 [quoted text clipped - 6 lines] > now that I've got 96 of them, I really need a function or a macro or > something. Can anyone help ? Thanks 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.  SignatureTeam EM to the rescue! http://www.team-em.com On 5/19/08 7:55 PM, in article carl-C3EF37.19551019052008@comcast.dca.giganews.com, "Carl Witthoft" <carl@witthoft.com> wrote: >> 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 [quoted text clipped - 16 lines] > "=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. On 5/20/08 9:59 AM, in article C458516F.4E91%dadams@forsyth.org, "Dany Adams" <dadams@forsyth.org> wrote: > On 5/19/08 7:55 PM, in article > carl-C3EF37.19551019052008@comcast.dca.giganews.com, "Carl Witthoft" [quoted text clipped - 25 lines] > 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. SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom 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. > 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 On 5/20/08 10:56 AM, in article C4585ECF.AC593%bob@nospam.com, "Bob Greenblatt" <bob@nospam.com> wrote: > On 5/20/08 9:59 AM, in article C458516F.4E91%dadams@forsyth.org, "Dany > Adams" <dadams@forsyth.org> wrote: [quoted text clipped - 36 lines] > 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 ? > On 5/19/08 7:55 PM, in article > carl-C3EF37.19551019052008@comcast.dca.giganews.com, "Carl Witthoft" > <carl@witthoft.com> wrote: > >> 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 [quoted text clipped - 12 lines] > > "=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. 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 SignatureTeam EM to the rescue! http://www.team-em.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.