+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    10-11-2005
    Posts
    13

    Angry 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

  2. #2
    Ed Ferrero
    Guest

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

    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...o&userid=28014
    > View this thread: http://www.excelforum.com/showthread...hreadid=475213
    >




  3. #3
    Registered User
    Join Date
    10-11-2005
    Posts
    13
    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

  4. #4
    Ed Ferrero
    Guest

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

    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...o&userid=28014
    > View this thread: http://www.excelforum.com/showthread...hreadid=475213
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1