range of data from different sheets

I'm having trouble creating a graph because the data is spread on different 
sheets.
I've got a new sheet for each month and I'd like to produce a trend graph. 
Do I have to bring each set of numbers onto the same sheet, or is there a way 
to have a range of data spread over various sheets?

I hope that makes sense!
0
stew (11)
6/24/2005 3:38:04 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
519 Views

Similar Articles

[PageSpeed] 50

For any given series, for any given set of values (x or y) the data 
must be on the same sheet.  Other than that, you can have data on 
multiple sheets.

So, for one series you could have the series name on one sheet, the x-
values on a 2nd sheet, and the y-values on a third sheet.  However, the 
x-values themselves cannot be on multiple sheets.  The same applies to 
the y-values.

For example, a series formula could look like:
=SERIES(Sheet3!$A$2,Sheet2!$A$2:$A$5,Sheet1!$B$3:$B$6,1)

Similarly, for another series the data could be on 3 more sheets.


-- 
Regards,

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

In article <08A7402B-B855-460E-83B7-DAC45A8FC722@microsoft.com>, 
SteW@discussions.microsoft.com says...
> I'm having trouble creating a graph because the data is spread on different 
> sheets.
> I've got a new sheet for each month and I'd like to produce a trend graph. 
> Do I have to bring each set of numbers onto the same sheet, or is there a way 
> to have a range of data spread over various sheets?
> 
> I hope that makes sense!
> 
0
6/26/2005 1:41:25 PM
You can use formulas to compile the values onto a single sheet:

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

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

SteW wrote:

> I'm having trouble creating a graph because the data is spread on different 
> sheets.
> I've got a new sheet for each month and I'd like to produce a trend graph. 
> Do I have to bring each set of numbers onto the same sheet, or is there a way 
> to have a range of data spread over various sheets?
> 
> I hope that makes sense!
0
6/26/2005 6:31:43 PM
Reply:

Similar Artilces:

point exchange to a different priv1.edb file
I have a snapshot of my priv1.edb file on a different drive. I know that exchange 2003 lets us me the priv1.edb file to a different location if the file isn't already there. Is there a way to have exchange point to a different priv1.edb and use it? On Thu, 5 Oct 2006 21:31:01 -0700, Han <Han@discussions.microsoft.com> wrote: > >I have a snapshot of my priv1.edb file on a different drive. I know that >exchange 2003 lets us me the priv1.edb file to a different location if the >file isn't already there. > >Is there a way to have exchange point to a differ...

Combo Box from data in another tab
Is it possible to create a combo box from data that's in another tab? I have a combo box that's from data in hidden columns. But now I need to move all that hidden data to another tab in the same file. But when I go into properties and go to the ListFillRange option, it won't let me enter another tab name. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 I have called data from another tab with a combo box by doing th following: First - name the range of the data on the other tab. This can be don with the shortcut on the toolbar (l...

Adding a new data series to an existing chart
I have a stacked bar chart that shows monthly sales by customer type. I want to add an additional data series to show the monthly forecast, to be displayed as a point or line against the existing stacked-bar data. Any ideas? Select the data you want to plot. Drag onto the chart. XL will pop up with a dialog box asking for some information. Provide it and you will be all set. -- Regards, Tushar Mehta MS MVP Excel 2000-2004 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article <1418C2D7-4DF6-4945-A08A...

Excel 2000
Hi! *First off:* I have created a spreadsheet that has a dynamic range an data validation. *Next:* i know i can add options to the 'dynamic range' and my dro down menu will add the option to its list. *Problem:* How can I make it so the user can add data into th validated data list/drop down menu rather than having the user addin it into the dynamic range? *Example:*this drop down menu indicates how often a system is updated weekly, monthly, etc. etc... i don't want to restrict it, so i want t make it so they can indicate their own time on how often those update occur. TIA -...

Help! page range prob in print
Hi I worked on print.Everythings working perfectly except one problem.ie. When i set the PageRange option to 'ALL' option and press print button..instead of displaying only the pages which has information ..its displaying that all pages from 1 to 65535 r going to be printed. How do i remove this problem? thanks, vani I thing you may failed to set the maximum number of pages to print. Check CPrintInfo::SetMaxPage(nMaxPage); method regds Jibesh -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspecto...

Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro be run to change the order to "first name, middle initial, last name"? A formula will do Assuming you have spaces between commas and names, =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)))&RIGHT(A2,FIND(",",A2,FIND(",",A2)))&", "&LEFT(A2,FIND(",",A2)-1) Then just copy down. If you want this more permanent, copy the cells with formulas, right click, paste special. -- Best Regards, Luke M *Remember to click &q...

How do I make the x axis data the y axis data?
My graph automatically makes certain data the y axis and other data the x axis. I need to just reverse it for the graph I am looking for. Any suggestions? SLG, One option is to reverse the positions of the data on the spreadsheet. Assuming an XY chart, you would change a setup like this: x y 5 4 4 2 5 3 7 4 6 5 to this: y x 4 5 2 4 3 5 4 7 5 6 If this doesn't work, can you post back to let us know what type of chart and data you're using? ---- Regards, John Mansfield http://www.pdbook.com "SLG" wrote: > My graph automatically makes certain data the y axis ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Prevent auto recoloring of chart when using Pivot Table data
I have crated a Pivot tabel with data I want to show in a Chart. My problem is when I refresh data the colours of the "data series" changes. For exaple I want one of my series "OK" always be shown in green colour, but after a refresh of pivot all colours changes. This problem is not only colouring. If I have made changes to my chart so that one series ar plotted on a secondary axis, and I change it to another chart type (e.g. the secondary should be shown as a line instead of column) this also changes when refreshing the data. Most irritating! Anyone that knows how to p...

converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct?? Excel should recognize a contiguous range as a table, or list. Include headings in the first row, and leave at least one blank row and column between the table and other items on the worksheet. You can use the built-in data form to add records to the table (Data>Form). Or,...

Data Migration Framework 08-04-04
Hi, I am not understanding something pretty fundamental regarding the Data Migration Framework (DMF). I am looking to migrate a limited number of records (approx 1K) from Goldmine into MS CRM as Account records. I understand that the DTS in SQL can be used to load the data into the CDF database. On page 73 of the Data Migration Framework Guide (v1.2) it states that you use the Goldmine export utility to extract data from Goldmine. However, how do you use DTS to load this data into the CDF database as Goldmine exports it into Excel? Also, using the export facility in Goldmine you can only e...

Summing of Different sites within a day
Hello! I have an employee attendance database for 2 sites, FL and AZ. My report is sorted first by date, then by site then by reason (there are 8, e.g. "Vacation") I have a sum in each Reason footer that gives me the total number of hours everyone at that site was out for a particular reason. I also have a sum in each date footer that gives me the total number of hours everyone at both sites was out for all reasons. What I need is this sum in the date footer to be broken up by Reason, regardless of site. Thank you for your help! I don't get how a "...

what the difference is?
Under Change styles > Style set, there are two options: Reset to quick styles from template, and Reset document quick styles What the difference is? If I reset to template, the document quick styles doesn't reset themselves too? -- geotso ---- ---- --- -- -- - - Please, remove hyphens to contact me ---- ---- --- -- -- - - I haven't tested this thoroughly, but I suspect there might be a difference with documents to which you have attached a template (that is, a template different from the original template used when creating t...

Referencing Sheet Tabs
Excel 2003. I produce a weekly cash report. Each new weekly report refers to several cells on the previous week's sheet. I produce each new sheet by copying the last one and changing its name. I then have to update the formulas so that they look at the now previous sheet. Is there some way to enter the sheet tab name into a formula so that the name of the tab is taken from another cell. Hi captain, Lets say cell A1 contains the sheet name cell A2 contains the row no cell A3 contains the column no of the cell you wish to find. Then =ADDRESS(A2,A3,,,A1) will give you the cell addre...

Data Range with more than value
I am trying to assign codes to one of my worksheets based on pricing. The problem that I am running into is that the data that I am taking my information has a range of pricing Example below PRICE POINT/RANGE PP $0.01 TO $1 PP0100 I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to a column that has pricing (i.e .95). This formula/function should give me the "PP" code. Break the range up into two columns (Data - Text to columns). Assuming you don't have gaps/overlaps in your price range (why would you?) your formula ...

Cells with same text but excel think it's different?
I have two cells with exacely the same text. but excel think it's different and not returning any value for my vlookup.. I used if statement and the result is showing the 2 cells are different. I checked format, space and even '. can someone please help? thanks. Do you have an 0 instead of a O? (Number zero, letter O) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Jason" <Jason@discussions.microsoft.com> wrote in message news:84F83A62-88D7-4292-9B2E-B8F34B737B48@mic...

"Ghost" sheet objects returned while using Excel.Application
I am trying to parse a spreadsheet using JavaScript and I'm finding "ghost" Sheet objects in the file. It seems that if the user copied and renamed the file and then deleted some tabs that those tabs are still accessible programmitcally. If I view the excel file in Excel or try to load it into SQL Server then the "ghost" tabs don't show up. But if I read it using the Excel.Application in JavaScript then they show up. Here's an excerpt of what I'm doing: var wb = XLS.WorkBooks.Open(fname); var ws = XLS.Sheets; var cell = null; var e = new Enumerator(XLS....

insert rows--->protected sheet
I have a protected sheet in Excel 2007, and I would like to write a macro that allows a user to insert a new row(s) that also copies the formulas (but not the text/data) from the previous row. When I searched for a possible solution, I found the following macro #2, which works perfectly, if the sheet is unprotected. I use the following code (macro #1) in another macro to unprotect a sheet temporarily to run a macro, then re-protect it. I select a row on the spreadsheet and run the macro InsertRowsAndFillFormulas_caller(). I get a "Compile Error: Invalid or Unqualified ...

How to change data range for Pivot Table
Hi, How can I make pivot table query for parameters like data range, I have done something like this in normal query by adding [Ask For Date:] in MS Query. Does anyone know how to achieve similar result for Pivot Table. regards Peter You can use the pivot table's page fields to filter the data. Or, import the external data to a worksheet, where you can use parameters, and base the pivot table on that. piotr.kabath@gmail.com wrote: > Hi, > > How can I make pivot table query for parameters like data range, I have > done something like this in normal query by adding [Ask F...

Sending data to SQL Server
Hi, I am building a solution to allow a user to upload shape data to SQL Server and I have a couple of questions. 1) Is it possible to include more attributes than just the shape data, but also details of the process model file such as it's title, version, etc? I could do with having the file name in every column of shape data for example. 2) It's quite a few clicks to export the data to an SQL database (about 5 or 6). Is there a way to have this feature available say next to the floppy disc save icon? Steve Since you're building a solution, then you understand t...

rearrange data by day
Hi I have daily data for some ten years listed downwards, and I woul now like to know if there is possible to rearrange them so that I ca get all the data for each day alone I.e. one group of just Monday dat and another group and just Tuesday data as one group, all the number are currently listed from A1 to A2000. I only Monday to Friday as m days All help is greatly appreciated!!! If my explanation weren&#8217;t good enough, this is what my data look like now Tuesday 12/31/2002 279,73 Monday 12/30/2002 279,73 Friday 12/27/2002 281,982 Thursday 12/26/2002 278,829 Wednesday 12/25/2...

histogram, wrong input range
when I try to put data into a histogram. It changes to the name of my worksheet. WHY????? Need help... Camilla Camilla - > when I try to put data into a histogram. It changes to the name of my > worksheet. WHY????? Need help... Camilla < Some range edit boxes show the worksheet name preceding the range you have selected. As an alternative to Excel's Histogram tool, especially for continuous-valued data (as opposed to discrete), you may want to try my Better Histogram free add-in, available at www.treeplan.com. - Mike www.mikemiddleton.com ...

how can i increase number of rows beyone 65536 in excel sheet
the maximum of number of rows that are available in an excel sheet are only 65536,i would like to enhance it to my choice. Plase mail me how can i do it ,either by writing a macro , or other way. please also mail me the entire procedure, where to correct and where to run the macro etc. You want the car washed and the dog taken out as well? There is no way of extending this, use a database or multiple sheets -- Regards, Peo Sjoblom "increasing number of rows in excel sheet" <increasing number of rows in excel sheet@discussions.microsoft.com> wrote in message news:5F6...

puzzling data mismatch error
I have a table where one of the fields contain values such as 26-14-2C-R1-K1 15-13-1C-R3-K6 and so on. this is a text field I am trying to find records based on the part: R1-K3, R2-K4, R5-K1 etc. I am getting weird data type mismatch error when searching with my query. For example this works: SELECT myfield as P FROM myTable WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) Like 'R1- K1' ORDER BY myfield but this one using a nested OR doesnt and fails with a data mismatch error: SELECT myfield as P FROM myTable WHERE myfield Is Not Null AND (right(myfie...

Creating a cell so data goes into a table
i am trying to create if possible a cell so that when i enter data into that cell and i press return it enters the data into a table, then deletes the cell where i originaly entered the data so i can enter some new data. do you have criteria as to where it goes into the table? "hally" wrote: > i am trying to create if possible a cell so that when i enter data into that > cell and i press return it enters the data into a table, then deletes the > cell where i originaly entered the data so i can enter some new data. no it needs to go into a table 10 x 10 and each figur...