Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart Automatically Captures New Data (XP, sr2)

    I have a "Final Report" spreadsheet that consists of charts only. Each chart gets its data from a separate spreadsheet file. I update charts by manually refreshing the Data Sources using menu options. Is there a way for the Final Report charts to automatically capture the additional rows of data that have been added to the source spreadsheets?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Automatically Captures New Data (XP, sr2)

    Yes, that is possible. It means you will have to change your chart
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Automatically Captures New Data (XP, sr2)

    You can create dynamic ranges (using OFFSET) and have the charts based on these names.

    Jon Peltier has an example when he shows how to Chart the Last 12 Months Dynamically

    Post back if you need additional info.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart Automatically Captures New Data (XP, sr2)

    Does that work with references to other workbooks?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Automatically Captures New Data (XP, sr2)

    I don't think so. I just tried it.

    I can make a valid name to an external workbook, but the chart won't take this as a valid chart range...

    I tried directly, I tried using a macro. I even tried making the name in that workbook and creating the chart, then renaming my "valid external range" to the internal range, and I also got the invalid chart reference.

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Automatically Captures New Data (XP, sr2)

    You are right of course. I didn't test whether it would work with an external ref.

    The only other method I would know then is to include formulas with links to the other workbooks that adjust to the amount of data and base the charts on those formulas.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Automatically Captures New Data (XP, sr2)

    In my playing:
    If you have a chart in the wkbA, and you want to include data from another workbook, wkbE, there are several options:
    1) you can create a direct link to the workbook for the static range in the source data (this works even if wkbE is closed):
    ='C:[wkbE.xls]Sheet1'!$A$1:$A$7
    1b) You can also create a name in wkbA (eg Cht1), and refer to this name from wkbA as the chart source:
    ='wkbA.xls'!Cht2

    2) in wkbE you can create a static name (eg named ChtX:
    =Sheet1'!$A$1:$A$7
    and use this named range as the sourcedata:
    ='C:[wkbE.xls]Sheet1'!ChtX
    This also works if the workbook is closed.
    2b) You can also create a name in wkbA (eg Cht2):
    ='wkbE.xls!'!ChtX
    and refer to this name from wkbA as the chart source:
    ='wkbA.xls'!Cht2
    This will work with a closed workbook.

    3) in wkbE you can create a dynamic range using offset (eg named ChtX2):
    =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),1)
    and use this named range as the sourcedata:
    ='wkbE.xls!'!ChtX2
    3b) In this case the workbook must be opened. I have been unable to do it with a closed workbook.
    You can also create a name in wkbA if desired.

    4) If you create a dynamic formula in wkbA which refers to wkbE:
    =OFFSET('[wkbE.xls]Sheet1'!$A$1,0,0,COUNT('[wkbE]Sheet1'!A:A),1)
    The dynamic formula is only good when wkbE is open. otherwise you get invalid reference dialog message.

    Even with wkbE open, excel will not allow (at least that I could figure out how) this name to be the source for the chart...


    Thus if you want a dynamic range in wkbE to be plotted in wkbA, the dynamic range must be created in wkbE, and wkbE must also be open.
    Steve

Posting Permissions

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