Charts not recognizing source data if original linked data is changed.

I am very frustrated by Excel (2003) at the moment. I'm relatively new
to using Excel in depth. I have always been able to work my way through
most intricacies and pitfalls. But now I have been working in a workbook
with linked sheets (and with linked workbooks too) and have two problems
that I cannot solve.

Right now, I have about 47 sheets that are set up as follows:

detail data: this is the raw data the the user enters
daily totals: this is calculated data from the detail sheets
summary sheets: further sums from the daily totals and a chart object
that charts these sums (a line or scatter chart)
(that accounts for 45 of the sheets)
a combined-summary sheet: this includes the data from all summary
sheets
an overall summary sheet: is the same as the summary sheets, but takes
totals from the combined-summary sheet

So, first problem: The workbook functions exactly as I want it
to...ONCE. Then, if any data is changed in the detail sheets all the
other data updates, but the charts fail to show any of the source data.
They are completely blank. I have calculations set to automatic. I have
all protections off (though I originally thought this was my problem
because I protected everything but the cells where I want users to
input raw data). I have Update Remote References on, but that shouldn't
matter since it's all one workbook. I even saved the file as a template,
which was always my intention, but when one user tried to plug in his
data, the charts didn't recognize the data. I've tried different chart
types. I've tried re-highlighting the source data. I've tried having
the charts on separate worksheets instead of as objects. Nothing I've
tried has worked. I looked in online Excel help to no avail. I have an
Office 2003 Bible, but can't find any section that discusses this.

Please help. This is so frustrating! This is my last resort before
reporting a bug to Microsoft and waiting for the update...

Second problem: In the same workbook, each chart's source data is units
that have an associated cost. My boss wants me to have the associated
display on the chart when you mouseover each data point. I'm sure I
could figure this out eventually, but don't have time after spending
days on my first problem.

I really appreciate any and all responses. Please show me what I'm
overlooking, so I can feel like a dolt, but a dolt with a functioning
workbook!

Thanks so much,
jlc


-- 
JLC
------------------------------------------------------------------------
JLC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28014
View this thread: http://www.excelforum.com/showthread.php?threadid=475213

0
10/11/2005 10:00:45 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
752 Views

Similar Articles

[PageSpeed] 12

Hi JLC,

Not too sure what the problem is - perhaps you can give us some more 
information, like;

Are your charts embedded in the summary sheets, or are they separate chart 
sheets?

What does the chart series formula look like? In one of my worksheets, I 
click on a series and see a formula like this =SERIES('KPI 
Report.xls'!Act,'KPI Report.xls'!Time_axis,'KPI Report.xls'!Month_Actual,2)

How are the summary sheets calculated? SUMIF formulas? ADO? Pivot Tables? 
INDIRECT(ADDRESS...?

Do you have one workbook or more? You seem to be saying that you have more 
at least one linked workbook - are all workbooks open when you change the 
data?

Are you using any VBA code?

Ed Ferrero
http://edferrero.m6.net/


>
> I am very frustrated by Excel (2003) at the moment. I'm relatively new
> to using Excel in depth. I have always been able to work my way through
> most intricacies and pitfalls. But now I have been working in a workbook
> with linked sheets (and with linked workbooks too) and have two problems
> that I cannot solve.
>
> Right now, I have about 47 sheets that are set up as follows:
>
> detail data: this is the raw data the the user enters
> daily totals: this is calculated data from the detail sheets
> summary sheets: further sums from the daily totals and a chart object
> that charts these sums (a line or scatter chart)
> (that accounts for 45 of the sheets)
> a combined-summary sheet: this includes the data from all summary
> sheets
> an overall summary sheet: is the same as the summary sheets, but takes
> totals from the combined-summary sheet
>
> So, first problem: The workbook functions exactly as I want it
> to...ONCE. Then, if any data is changed in the detail sheets all the
> other data updates, but the charts fail to show any of the source data.
> They are completely blank. I have calculations set to automatic. I have
> all protections off (though I originally thought this was my problem
> because I protected everything but the cells where I want users to
> input raw data). I have Update Remote References on, but that shouldn't
> matter since it's all one workbook. I even saved the file as a template,
> which was always my intention, but when one user tried to plug in his
> data, the charts didn't recognize the data. I've tried different chart
> types. I've tried re-highlighting the source data. I've tried having
> the charts on separate worksheets instead of as objects. Nothing I've
> tried has worked. I looked in online Excel help to no avail. I have an
> Office 2003 Bible, but can't find any section that discusses this.
>
> Please help. This is so frustrating! This is my last resort before
> reporting a bug to Microsoft and waiting for the update...
>
> Second problem: In the same workbook, each chart's source data is units
> that have an associated cost. My boss wants me to have the associated
> display on the chart when you mouseover each data point. I'm sure I
> could figure this out eventually, but don't have time after spending
> days on my first problem.
>
> I really appreciate any and all responses. Please show me what I'm
> overlooking, so I can feel like a dolt, but a dolt with a functioning
> workbook!
>
> Thanks so much,
> jlc
>
>
> -- 
> JLC
> ------------------------------------------------------------------------
> JLC's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=28014
> View this thread: http://www.excelforum.com/showthread.php?threadid=475213
> 


0
mail953 (171)
10/13/2005 3:38:12 AM
As I mentioned, I have this problem if charts are embedded as objects on
the summary sheet OR if they are separate sheets. NOW, I have each chart
as an embedded object in the summary sheet and all source data comes
from a single worksheet.

The series formula looks like this: ='All (summary)'!$C$28:$I$28

Summary sheet (1) cells are simple cell references to (2) single cells
that are also simple cell references to (3) (a) single cells OR (b)
single cells that contain a manual sum formula of cells from another
sheet: ='54th (d)'!F6+'54th (d)'!I6+'54th (d)'!L6+'54th (d)'!O6

I originally had this set up as multiple workbooks and had the same
problem. Now, all data is in one workbook on multiple sheets.

No VBA code is used. (I am just starting to learn VBA and am sure my
workbook would be far more elegant with its help.)

One note: Sometimes, if I change the chart type, the series appears.
This helps in the short term, but can't be maintained long term.

Please let me know if there's any more information needed.

I really appreciate your help.

-JLC


-- 
JLC
------------------------------------------------------------------------
JLC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28014
View this thread: http://www.excelforum.com/showthread.php?threadid=475213

0
10/13/2005 6:57:30 PM
Hi JLC,

Thanks for the clarification.

I see that you have () charachters in the sheet names - these should not 
cause a problem, but check that you do not have an apostrophe in any sheet 
name - that causes BIG headaches with formulas like ='54'th (d)'!F6

Other than that, I can't see what causes the behaviour you describe. Send me 
the workbook if all else fails.

Ed Ferrero
http://edferrero.m6.net/


>
> As I mentioned, I have this problem if charts are embedded as objects on
> the summary sheet OR if they are separate sheets. NOW, I have each chart
> as an embedded object in the summary sheet and all source data comes
> from a single worksheet.
>
> The series formula looks like this: ='All (summary)'!$C$28:$I$28
>
> Summary sheet (1) cells are simple cell references to (2) single cells
> that are also simple cell references to (3) (a) single cells OR (b)
> single cells that contain a manual sum formula of cells from another
> sheet: ='54th (d)'!F6+'54th (d)'!I6+'54th (d)'!L6+'54th (d)'!O6
>
> I originally had this set up as multiple workbooks and had the same
> problem. Now, all data is in one workbook on multiple sheets.
>
> No VBA code is used. (I am just starting to learn VBA and am sure my
> workbook would be far more elegant with its help.)
>
> One note: Sometimes, if I change the chart type, the series appears.
> This helps in the short term, but can't be maintained long term.
>
> Please let me know if there's any more information needed.
>
> I really appreciate your help.
>
> -JLC
>
>
> -- 
> JLC
> ------------------------------------------------------------------------
> JLC's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=28014
> View this thread: http://www.excelforum.com/showthread.php?threadid=475213
> 


0
mail953 (171)
10/14/2005 12:29:20 AM
Reply:

Similar Artilces:

Linking Database to invoice/email template
I have designed my database and on inputting a new supplier I would like Access to generate an invoice in excel and an email in outlook express, is this at all possible as can't seem to find anything like this in my step by step book. Neets <Neets@discussions.microsoft.com> wrote: >I have designed my database and on inputting a new supplier I would like >Access to generate an invoice in excel and an email in outlook express, is >this at all possible as can't seem to find anything like this in my step by >step book. Actually I'd create the invoice in a PDF...

Change Forecolor based upon if statement
On a form I have two controls named [Con1] and [Con2]. On Exit of a third control [ConComm], I am trying to change the font of Label304 only if [Con1] And [Con2] are Not empty or Not Null. So If both [Con1] and [Con2] have entries, then Me.Label304.Forecolor=50787 Else (if there are no entries) Me.Label304.Forecolor= 6697728 rbb101 wrote: >On a form I have two controls named [Con1] and [Con2]. On Exit of a third >control [ConComm], I am trying to change the font of Label304 only if [Con1] >And [Con2] are Not empty or Not Null. > >So If both [Con1] ...

chart with %
How I create automatically chart similar to PIE but "columns with % " without create a new table with % numbers ? thanks Marina ...

How do I sort data in a spreadsheet
I have a spreadsheet that has a number assigned to each name and it was made in aphabetical order. I want to change it to numerical order. How do I do this? You can't in Publisher. You can try pasting the data into Excel, sorting it and then paste it back into Publisher. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "stmary" <stmary@discussions.microsoft.com> wrote in message news:92E24369-2E8E-4B02-899D-95A4E389D6D2@microsoft.com... >I have a spreadsheet that has a number assigned to each name and i...

Giving Access to Mailbox w/o Changing Permission on each folder?
Can I do something in exchange admin so that a user can see all the folders of another user without going through each folder and adding user to permission? Can I change the primary account to the other users once the old employee leaves? They have too many folders to go through each and do this. Thanks Q268754 "How to Assign Users or Groups Full Access to Other User Mailboxes" "B-Dog" wrote: > Can I do something in exchange admin so that a user can see all the folders > of another user without going through each folder and adding user to > permission? Can...

How pull data always from same location?
I want worksheet AA to pull data from a set location of worksheet BB. Worksheet BB is edited by a user who may add, delete and move the rows. If I make 'AA'!A10 set to ='BB'!A10 that works fine until my user moves the data on BB to row 50. At that point my 'AA'!A10 shows a value of ='BB'!A50. How do I set things up such that even after my user moves data to row 50 my 'AA'!A10 continues to pull from ='BB'!A10? Thanks =INDIRECT("'BB'!A10") HTH, Bernie MS Excel MVP "xrbbaker" <xrbbaker@discussions.microsof...

Removal of broken Hyperlinks from data copiedfrom a web page in to an Excel Spreadsheet
I have copied over 600 + lines into excel from a web page data base. I have cells which appear as hyperlinks (blue underlined text). When I put the insertion point over the cell, I get the little "hand" and a hyperlink box opens but is blank. I do not want the text in the cells to be hyperlinks nor do I want to open any hyperlinks. I went to the edit menu to select links to remove all but links is grayed out. I can remove each one, one by one, by using the arrow key to move into the cell, doing a right click and select, remove hyperlink. This works, but will not accept multiple...

Removing link to another workbook
Hello, I originally did a VLOOKUP into another workbook to populate a cell. Once that data is retrieved and populated, I no longer need the link to the other workbook. Is there any way to remove this link and keep the data in the cell? The workbook where this data comes from will not be present when I supply the spreadsheet to others and when you open the xls it pops up an annoying msg asking whether you want to refresh the linked data. And, like I said, I only need to populate the data once, then keep it. Any help is appreciated! Thanks -- ATC ------------------------------------------...

how to keep original email in inbox after replying to it?
At the moment the original email disappears when I reply to it. this started to happen at the beginning of this week (29 March). How do I get the email to remain in my inbox? Thanks. "laughter" <laughter@discussions.microsoft.com> wrote in message news:01D44D20-25D3-4FFF-A159-7692D0E2EE80@microsoft.com... > At the moment the original email disappears when I reply to it. this > started > to happen at the beginning of this week (29 March). How do I get the email > to > remain in my inbox? > > Thanks. Check your View. Set it to messa...

Loading data from Excel to Oracle
Hi All, I would like to load an Excel spreadsheet directly into a single table in Oracle. Both the spreadsheet and the table have the same ordering of columns and have compatable data. I am aware that a way to achieve this would be to convert the .xls file into a .csv file and then use sql loader. However, I am interested in doing this in a quicker/easier way directly from Excel, eg. by adding in some functionality and clicking on a new button for loading into the database. We are all able to read from a database in Excel by importing external data. Surely there is a way to change that...

How can I "undo" an entry on a form/subform while still entering data ? 11-18-07
Hi I posted this question a couple of days ago and got 1 response but couldn't read it. Hopefully someone will have and answer. I have a form/subform for entering invoice data. What can I do if I realize that I have already entered the invoice ? Example: I enter the main form data and enter 5 lines of detail on the subform and then I realize that I have already entered this invoice before ? I would love to have a button that I could click that "undoes" the entire entry. How can this be accomplished ? Thanks Mark <mthornblad@gmail.com> wrote in message news:81271...

Changing the quata msgs
Good 3rd Party Tool Found http://workspaces.gotdotnet.com/quotamsgsvc ...

How do I get the combo box to link with costs field in the table?
I have a database which I am trying to figure out. I have a field in the table which is the names of the cars and there is a price field. How do I link both of them up so that when I create a form and select the different car the different prices can appear. Can someone offer some help with this Please! Samiullah, We can't help until we know how you have your table set up. Is the CarName and the CarPrice in the same table? If they are in different tables, do you have a unique key field in each table that "relates" the CarName in one table with a CarPri...

How can I change default for inserting a picture
I edit a newsletter and I insert lots of pictures. I insert the pictures but I ALWAYS want them to be TIGHT for text wrapping and I want them to have a red border with a weight of 1 1/2. I always do each picture one at a time to change the format. Sometimes I have 90 pictures. Is there a way to tell word to always insert the picture the way I want? -- Thanks Very Much! Art Nittskoff Recent versions of Word let you choose the default wrapping format for pictures. For example, in Word 2007, you'll find the option here: Office button | Word Options, Advanced category: &...

point of intersection of two lines on line chart
Hello, is there a way to locate the exact point of intersection of two lines on line chart?? Dave - Andy Pope has a helpful web page: http://andypope.info/charts/intersection.htm - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Dave wrote: > Hello, is there a way to locate the exact point of intersection of two lines on line chart?? ...

Analysing data through pivot
Hi, I am trying to convert the payroll data I receive from my clients into a csv file that I import in to my payroll program. Ideally I would like it to be all automatic (marco). The problem lies in the variable nature of the information I receive. Here is the basic layout: NAME | ID | Basic pay | Commission | Overtime Bob 2 500 300 50 James 3 400 20 Sarah 4 500 And so forth THe layout I am trying to achieve is the following: Bob 2 Bas...

How to avoid coping a link when coping a formula from another wor.
When I copy a cell with a formula in it from one workbook to another I can not avoid coping the link back to the source workbook. Others in the office don't seem to have this issue. Is there some switch which is causing this to automatically happen to me? ...

Dynamic bar chart, must ignore "n/a" data...
Okay, gang, here's what I am trying to do. Assume I cannot use macros or pivot tables (the end chart must be usable by folks with ZERO excel ability.) Formulas only. (I know this would be easy with pivot tables.) Assume I have a table that is calculating how many pieces of fruit I pick. I have a column (A) that is called FRUIT. The user of the spreadsheet picks the type of fruit from a validated drop down list in (A) and then enters the number of pieces picked in (B). so the data might look like this: APPLE 2 APPLE 3 PEAR 2 PEACH 2 APPLE 4 Where each row is a different day...

Colors changing
When I open a saved file, the colors have changed to a variety of pinks. This is really bad. Help! A small child turns to Ed, and exclaims: "Look! Look! A post from June <anonymous@discussions.microsoft.com>!"... > When I open a saved file, the colors have changed to a variety of > pinks. Read here: http://www.mvps.org/the_nerd/Publisher/FAQs.htm -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

Transfers not working after changing Categories?
Hi, we have serious issues of store transfers not completing and the last time the issue arose it was right after we changed the names of the departments and categories in headquarters. We are restructuring our database so we are making changes to: Categories, Suppliers, Prices, Item Names and Cost. I have made sure to create worksheets to update these changes in our stores from Headquarters, but all of the sudden, our inventory transfers aren't going through: *It's not a connection problem as all of the worksheets have been successfully downloaded and sales info was updated...

copy entire contents worksheet into new workbook, link but editabl
I need to copy the entire contents of a worksheet which is used by several collegues who need to see it looking the same always (headers in the same order,etc). In the new worksheet (workbook) I will rearrange the data for other purposes. However, I will need the data to be kept up-to-date as per the source worksheet. ...

Changing letters to symbols
After typing (c) in a cell Excell automatically changes the characters to the copyright symbol. Where do I disable this? Menu Tools>AutoCorrect... and delete teh corresponding (probably the first) entry in the list. Regards, KL "Steve F" <Steve F@discussions.microsoft.com> wrote in message news:B9DC4D0D-A63A-4BF6-B608-17357ADFB672@microsoft.com... > After typing (c) in a cell Excell automatically changes the characters to > the > copyright symbol. Where do I disable this? ...

Change default font in week view?
Outlook 2003 How do I change default font in yhe calendar's week view? -- Thanks... Bob ...

How to use XY Chart Labeler
As some of you have recommended here, I downloaded and installed the XY Chart labeler from AppsPro (http://www.appspro.com/). There does not seem to be a manual for it, so how do I get started using it? Thanks. -- ================================================= Do you like wine? Do you live in South Florida? Visit the MIAMI WINE TASTERS group at http://groups.yahoo.com/group/miamiWINE ================================================= To install the add-in, double-click on the XYChartLabeler.exe that you downloaded. Then, in Excel, choose Tools>Add-ins If XY Chart Labeler isn'...

WMP 12 on Windows 7
I am attempting to get all my digital media centralized and organized, got all the DVD ripped into Media Center now I am attempting to get my music synced as well. Problem: Windows Media Player and Media Center is not picking up the mp3's in their respective locations. I have mapped and remapped them to the approriate location and it isn't pulling them into the library. Ran the MS diagnostics via troubleshooting on both the library and media player. It states I have a corrupt media library, and it gives me no hints on how to correct the issue. Any suggestions? ...