+ Reply to Thread
Results 1 to 4 of 4

Data source formula in Graph

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Data source formula in Graph

    Hello Excel friends, I'd really appreciate some assistance.

    I am using Excel 2016 for Windows on my PC.

    In my SampleChart spreadsheet, I have a graph on my CHART sheet. Next to the graph on that sheet in cell $I$3 I have a Drop Down that gives me two choices, JAN or FEB, the names of my other two sheets.

    From my graph, I go to my Chart Filters > Select Data to the 'Select Data Source' window. In the "Chart Data Range" I need to enter the range to include my source sheet selection from $I$3 followed by the range $A$1:$D$4. I currently use 'FEB'!$A$1:$D$4 but have to manually change the sheet instead of using the drop down.
    I need to use the drop down.
    So how can I change the 'FEB' to an INDIRECT() or whatever works to complete this? Of course the combination of single and double quotes is the toughest part.

    Thanks for any help offered.
    Attached Files Attached Files
    Last edited by GregM56; 11-04-2019 at 05:32 PM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Data source formula in Graph

    as memory serves you cannot use INDIRECT for a Chart Data Range
    i.e. even if you use via an intermediate (and fully qualified) named range, leveraging the INDIRECT, the Chart will still convert it to a fixed address.

    others, e.g. Andy Pope, may be able to tell you otherwise - I am not a Chart whizz.

    As an alternative, have you considered using a Pivot Table + Chart ? I don't think that has the same issues...

    e.g. if in your file you put "Series" in A1 on both Jan & Feb sheets then, configure a Name:

    Name: _Chart_Rng
    RefersTo: =INDIRECT("'"&CHART!$I$3&"'!A1:D4")

    you can then create the Pivot Chart using _Chart_Rng as the source (it will create the Table first, to which you can add the Chart via Analyze tab on the Ribbon [with Pivot active])

    as you change I3 so your Pivot Table & Chart will update, once refreshed... you can, if desired, add a VBA Worksheet Change Event (for I3) to refresh the Pivot as and when the drop down is updated.

    all of the above present in the attached.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Data source formula in Graph

    Instead of a drop down list you could use a Table (not Pivot) + Slicer.
    To use a Table you would need not use one sheet / month, but add all months to one sheet.

    See attached file.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Re: Data source formula in Graph

    Thank you BOTH very much for your reply and assistance.
    Good day!
    Last edited by GregM56; 11-06-2019 at 09:03 AM. Reason: To express appreciation to all replies

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Different graph description then in data source
    By olwy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-07-2014, 03:49 AM
  2. Make a graph that that uses source data from another sheet
    By ArtVandelay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2012, 09:12 AM
  3. Changing Graph Data Source
    By just_some_guy in forum Excel General
    Replies: 3
    Last Post: 07-20-2011, 03:24 AM
  4. Changing data source of graph
    By shellmachine05 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2009, 03:07 AM
  5. Conditional Graph Source Data
    By benno87 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-21-2009, 05:58 AM
  6. Graph Source Data
    By Brian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2006, 12:15 AM
  7. Excel graph source data
    By RRRBBB in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-09-2005, 01:05 AM

Tags for this Thread

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