create chart with data from across seperate worksheets

i am currently trying to create a line chart using data found in different 
worksheets, but in the same workbook. although i can carry this out by 
cutting and pasting all the required data into the same worksheet whenever i 
try to pick the data from several or even two sheets i get a error message.  
i would've thought i'd be able to pick this up seperately...as you can for 
any other formula.
0
Diz (3)
5/12/2006 1:26:02 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
362 Views

Similar Articles

[PageSpeed] 47

Hi,

See Jon's page for information.
http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

Cheers
Andy

Diz wrote:
> i am currently trying to create a line chart using data found in different 
> worksheets, but in the same workbook. although i can carry this out by 
> cutting and pasting all the required data into the same worksheet whenever i 
> try to pick the data from several or even two sheets i get a error message.  
> i would've thought i'd be able to pick this up seperately...as you can for 
> any other formula.

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
5/12/2006 1:43:40 PM
I'm trying to extend my chart of sheet1 to include the data in sheet2 
using the data source chart properties box.

The working formula for sheet1 is as follows.
=Sheet1!$A$3:$IB$3

I've tried the following formulas to try and pick up the sheet 2 data and it 
doesn't work.
=Sheet1!$A$3:$IB$3;sheet2!$b$3:sheet2!$d$3
=Sheet1!$A$3:$IB$3,sheet2!$b$3:sheet2!$d$3

Any easy ways to do this correctly?

Thanks
Steve

p.s., My working values for the sheet1 chart are:
date range is =Sheet1!$A$3:$IB$3
series names is =Sheet1!$B$3:$IB$3
series values is =Sheet1!$B$3:$IB$3


"Andy Pope" wrote:

> Hi,
> 
> See Jon's page for information.
> http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html
> 
> Cheers
> Andy
> 
> Diz wrote:
> > i am currently trying to create a line chart using data found in different 
> > worksheets, but in the same workbook. although i can carry this out by 
> > cutting and pasting all the required data into the same worksheet whenever i 
> > try to pick the data from several or even two sheets i get a error message.  
> > i would've thought i'd be able to pick this up seperately...as you can for 
> > any other formula.
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> 
0
Beckwith (1)
12/22/2007 4:58:00 PM
You cannot combine data from multiple sheets into a single entry box in the 
Source Data/Series dialog. You will need to make some kind of summary table 
to use for the source data. Here's more information:

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

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


"Steven Beckwith" <Steven Beckwith@discussions.microsoft.com> wrote in 
message news:08ABC5E2-1866-45F0-9291-563464B75714@microsoft.com...
> I'm trying to extend my chart of sheet1 to include the data in sheet2
> using the data source chart properties box.
>
> The working formula for sheet1 is as follows.
> =Sheet1!$A$3:$IB$3
>
> I've tried the following formulas to try and pick up the sheet 2 data and 
> it
> doesn't work.
> =Sheet1!$A$3:$IB$3;sheet2!$b$3:sheet2!$d$3
> =Sheet1!$A$3:$IB$3,sheet2!$b$3:sheet2!$d$3
>
> Any easy ways to do this correctly?
>
> Thanks
> Steve
>
> p.s., My working values for the sheet1 chart are:
> date range is =Sheet1!$A$3:$IB$3
> series names is =Sheet1!$B$3:$IB$3
> series values is =Sheet1!$B$3:$IB$3
>
>
> "Andy Pope" wrote:
>
>> Hi,
>>
>> See Jon's page for information.
>> http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html
>>
>> Cheers
>> Andy
>>
>> Diz wrote:
>> > i am currently trying to create a line chart using data found in 
>> > different
>> > worksheets, but in the same workbook. although i can carry this out by
>> > cutting and pasting all the required data into the same worksheet 
>> > whenever i
>> > try to pick the data from several or even two sheets i get a error 
>> > message.
>> > i would've thought i'd be able to pick this up seperately...as you can 
>> > for
>> > any other formula.
>>
>> -- 
>>
>> Andy Pope, Microsoft MVP - Excel
>> http://www.andypope.info
>> 


0
jonxlmvpNO (4558)
12/23/2007 4:00:51 PM
Reply:

Similar Artilces:

creating a chart not working
my charts all come out with just gray lines no matter what I do, I know I am setting it up wrong, but have tried several ways, is there a trick? is this when you print it or on your display? make sure your print options say to pringt in color and not grayscale. "samm" wrote: > my charts all come out with just gray lines no matter what I do, I know I am > setting it up wrong, but have tried several ways, is there a trick? ...

How to convert CString object to data type?
How can I convert CString from a EDIT control Dialog to data type for calculation? thanks, Take a look at _stscanf/_ttoi. --- Ajay Y Xu wrote: > How can I convert CString from a EDIT control Dialog to data type for > calculation? > > thanks, Hi Y Xu, Can you be more specific , what type do you want it to be converted to ? /Kiran Y Xu wrote: > How can I convert CString from a EDIT control Dialog to data type for > calculation? > > thanks, On 8 Jun 2006 07:31:57 -0700, kiran.inbng@gmail.com wrote: >Hi Y Xu, > >Can you be more specific , what t...

Sum of data on a per month basis
I can't figure out how to do this. I think SUMPRODUCT might be the key but I can not figure it out. I need to extract the monthly sums of expenses incurred from a range of data. The data set has two columns Date and Price. The data changes throughout the year and I have had to adjust the sums ranges by hand and it has led to some human error on my part. Using functions I want to be able to automatically get total each month without manually having to adjust the ranges in the data set. I am using Excel 2007. Thanks so much. Suppose your dates are in column D and your expenses in co...

Range formula possible to update summary page with new entries when new sheets created?
I'm not sure what this would entail but is there a way to fix our summary pages to include formulas or whatever that more automatically update links from the sheets linked to them when new sheets are added to each workbook? The reason I ask is because my colleagues are dealing with four workbooks that each have about 15 worksheets minimum. Every time a new employee is hired, a new employee sheet is created by copying one from another employee and modifying the entries to reflect the new person. Unfortunately, the summary sheet doesn't automatically include working links when they co...

How do you set all graph data points to black (multiple series)?
I have multiple series on a chart, all with different marker shapes as desired. How do I globally set all the data point colors to black without picking each series in turn and setting a custom color. You can't do it using the native Excel interface, but a little macro will handle it: Sub PaintItBlack() Dim srs As Series For Each srs In ActiveChart.SeriesCollection srs.Border.ColorIndex = 1 srs.MarkerBackgroundColorIndex = 1 srs.MarkerForegroundColorIndex = 1 Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and ...

man-hour chart.
i have a question. i am working for an office. Where i have 32 labors, working under 4 seniors. i want to create an individual man hour chart and an individual budge chart for all the 7 seniors. What all fields i should include? And what are the formulas? Can i somehow relate these two charts? hope my question make sense. Regards, Deepika. EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks #17 http://www.eggheadcafe.com/tutorials/aspnet/f6b198d6-5475-47f6-ad65-add4829715f8/dr-dotnetskys-cool-net.aspx ...

Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable Data. How to expand this Data range to A1:D2 after using the DataForm to add one row data in the worksheet?? The same, the Data range should be A1:D3 when adding one more data row... million thanks Hi use the following formula in the name definition dialog =OFFSET($A$1:$D$1,0,0,COUNTA($A:$A)) >-----Original Message----- >I first created a variable with A1:D1, four columns with named variable >Data. How to expand this Data range to A1:D2 after using the DataForm to add >one row data in the worksheet?? The...

Concatenate mid, left then right data
Hi, I have searched other messages and my brain must be on holiday because I cannot get any of the answers I found to work for me. What I have is a column of data which has to now be changed for GL Reconciliation, looks like this- SMITH J_000063MVINS JOHNSTONE K_013565MVINS And I now need it to look like this- 000063,J SMITH,MVINS Most probably very simple but it still eludes me..... Any help would be much appreciated. Thank You Hi Try =MID(A1,FIND("_",A1)+1,6)&","& MID(A1,FIND("_",A1)-1,1)& LEFT(A1,(FIND("_",A1)-3))& ","...

Why do Data markers in Excel 2003 appear to be bold?
When crating charts in Excell 2003 the Data Markers (numerous points) appear to be bold and blurry. This does not occur in Excel XP. Marker size is set to 2. ...

How do I link columns so data flows from 1 column to another like.
Is there a way to link columns in Excel, so that data that's entered flows from one column to the next? There's a function similar to this in Quark. Anybody know anything about this? Regards, If I understand the question correctly, and the information is being typed into the cells, you could try this: Select a block of cells, say A2:C10 Then, type a value. Press enter. If yuo press enter after each value entered, when you enter a value in A:10 and press enter, you're active cell will be B2. tj "M. Frazel" wrote: > Is there a way to link columns in Excel, so t...

Changes on Chart
I am aware that any change in the data table will automatically changes the linked chart, is there any programm in Excel that can do this the other way around, i.e that gives the possibility of changing the shape of the chart on the chart itself rather than on the data table so that data table changes automatically with the change on the shape of the graph? -- tarig Hi, In older version of excel, not xl2007, the chart had a goal seek function. You could grab a point and move it in either the horizontal or vertical plane. The linked cell would automatically be updated. For an xy-scatt...

Exclude data from field, when another field equals a certain numbe
Hello, I have 2 fields. I have Job # and GL #. I need to exclude from the GL field, values that are 1234, but ONLY if the Job # = 999999. I have tried using this formula in the criteria of the GL# field in my query: IIf([Job #]=999999,<>1234,[GL #]) and instead of 999999 still showing up in the query and excluding GL# 1234, 999999 doesn't show up at all! What am I doing wrong?? Thank you!!!!!!!! MN IIf([Job #]<>999999, [GL #], IIf([GL #]=1234, Null, [GL #])) I'm assuming by exclude that you mean Null. -- Jerry Whittle, Microsoft Acce...

Opening disk containing Excel worksheet
I saved an Excel worksheet to a disk in the a drive. Later I opened it to edited the material, then resaved it to the disk. When I tried to open it again, it would not open. I received an error message stating that "The file may be read-only or you may be trying to access a read- only location." Other files on the disk do open. How can I either open the file, or at least access the contents so that I can have it retyped? I find the following thread to be fairly comprehensive. http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&...

Data Charting Correctly
We are working with a table that has a Sell Price, List Price, and Profit Margin. When we try to chart it, it looks like it is adding the "Sell Price" and "List Price". The "Sell Price" which is in row 1, is plotted correctly. The "List Price" looks to be the total of the two. The "Profit Margin" is plotting with the "List Price". Is it in the way we have selected the table to chart? Does anyone have any suggestions? Choose the line chart option in the first column, not the stacked line chart option in the second. - Jon ...

How do I create an alert of expiration date in Excel?
I created a spreadsheet to track documents. The last column is for expiration dates of documents. How can I set up an alert when the expiration date is approaching? Use conditional formatting in the cell. For example, with the date in A12, something like Formula is =TODAY()+7>=$A$12 as the condition. Then choose a pattern. "kachnycz" wrote: > I created a spreadsheet to track documents. The last column is for > expiration dates of documents. How can I set up an alert when the expiration > date is approaching? ...

Organization chart
I'm trying to build an org chart from an access 2000 database using Visio 2000. The database contains Greek text fields. The text appears as question marks. Anyone know how to do this ? I'm not sure you will get answers from this forum... I have asked the same question in multiple ways, but no one ever addresses the basic question: Why can't Visio access, use, print, view, or otherwise deal with any other font type than TTF, and even if it a TTF, only the first 256 characters of the font can be used (which makes useless the Unicode logic). (see previous post). I have had to w...

Problem with mixed trend and XY chart
I have a number of large spreadsheets used for analysing quality data On each table there are several trend charts showing results over time On each chart I have also added control limits set up as XY ranges. Initially all worked well.....but..... for some reason when I filte the lists now (to select specific products) the x axis labels (set u as a named range) expand to fill most of the graph space. Without the XY ranges it all works fine but with them in place i doesnt. When I check the axis format I also find that I can no longe change the number of categories between tick marks. What am...

Colouring Chart backgrounds
Hello, I would like to colour the background of a chart with 3 different horizontal stripes of colour. I have tried drawing 3 rectangles on it but cannot send it to the back, therefore it covers my actual chart info. Is there a way of doing this? Many thanks, Amanda Hi Amanda, Take a look at Jon Peltier's example, you should be able to adapt it. (http://www.geocities.com/jonpeltier/Excel/Charts/ColoredQuadrantBackground.html) Amanda wrote: > Hello, > > I would like to colour the background of a chart with 3 > different horizontal stripes of colour. I have tried &...

import data from access table into a existing table
I need to map data from am existing access database into a existing staging database. The access database only has the data I need, so the mapping is not a one to one, The SQL table many more columns so I need to do some manual mapping. Is there a way to do this throiugh the import wizard or do I need to write some type of query to manual insert the data? Thanks in advance. -- Jay Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1 Hi This is SQL Server forum, you will be better of to ask on Access forum "Jay via SQL...

Static reference to worksheet in formula
I have several Documents. For example one is called Budget 2007, Budget 2008. In each of these I have worksheets for each month, Jan, Feb, etc... I also have a worksheet called Year. In the Year I have cells that reference the month worksheets. For example I might have =Jan!$B23 My problem is that when I now copy the worksheet Year to the new file the cell reference is ='C:\[BUDGET 2007.xls]Jan'!$B$23 I can't edit the links because I do have some formulas which I want to reference previous years and otherwise will create lots of circular references. 2 questions I have. 1)...

Is it possible to create a mailing list in excel, not importing d.
I would like to create a mailing list in excel, entering each address in, not importing it from somewhere. Can excel do that? Or do I have to import the data, entering the addresses in a different software and doing some sort of mail merge with excel? Thank you in advance for your answer. If you have addresses in excel you can use word to mailmerge and use excel as source http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Regards, Peo Sjoblom "Claire Bardot" <Claire Bardot@discussions.microsoft.com> wrote in message news:C0BE56B4-AC5A-4D08-85E1-D6C262EE6080@mi...

Want to create sliders to change cell values in EXCEL for PC
As a math teacher, being able to view chart/graph changes as equation parameters change is an extremely valuable tool. An article in the NCTM Mathematics journal describes this capability using "Scroll Bar type Sliders" in EXCEL 2004, which is evidently for MAC's. I have a PC with EXCEL 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in th...

Pulling data from one sheet to another
I'm having problems with retrieving certain data from one sheet to another and I don't know if it is possible. See the below data as an example of what I'm using: VERSID DOC LH EN VELS COUNT 1233 Duplex 100 500 503 G 1233 Duplex 101 501 520 I 1675 Simplex 100,105 500 520 G 1675 Simplex 101,106 501 503 I 1674 Simplex 100,105 500 520 G 1674 Simplex 101,106 501 503 I Versid numbers are unique to each set of data, however the sheet I want to tie this up with use a different set ...

Data Validation to range names for Chart Source Data
I have 15 Named Ranges. I'd like to have a drop down above one chart to select one of the names and the chart updates. How? Please help It sounds like a pivot chart would give you the dynamic ability you are looking for. If you build a Pivot table and chart you can drag the series that you would like into the data range or have them on top to filter by. Another option would be to use an advanced filter for your data. Using this would also change the chart as you changed the filter. Tim "Candyman" wrote: > I have 15 Named Ranges. I'd like to have a drop down ...

Line chart with value on the line
Hi, I have implement a line chart by thousands of data rows for a stock. Now I want to show values on 10 particular points with the lable E.g, one extra column for displaying the label. TradeDate Ticker Close Label 04/01/1999 1234 63.67 05/01/1999 1234 63 06/01/1999 1234 63 Mr. A buy 07/01/1999 1234 67.33 08/01/1999 1234 68.33 11/01/1999 1234 68.67 Mr. B buy Does anyone teach me how to do this ?? thanks Ginola "Ginola" <ginola@fake.email.com> schrieb im Newsbeitrag news:3fdc1aa6.8209734@msnews.microsoft.com... > Hi, > > I have implement a line chart b...