+ Reply to Thread
Results 1 to 5 of 5

how to make line graph dynamic based on date ranges?

  1. #1
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    how to make line graph dynamic based on date ranges?

    Hi I would like to make the graph dynamic based on a start date and end date, how can this be done?

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,890

    Re: how to make line graph dynamic based on date ranges?

    I gave static names to the start and end dates. I also added a couple of helper cells to do some calculations. These could be hidden elsewhere in the workbook or rolled into the offset commands that define the dynamic ranges.

    To get information on named dynamic ranges and dynamic plotting, see these articles: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges and http://www.utteraccess.com/wiki/inde...namic_Charting.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,890

    Re: how to make line graph dynamic based on date ranges?

    I gave static names to the start and end dates. I also added a couple of helper cells to do some calculations. These could be hidden elsewhere in the workbook or rolled into the offset commands that define the dynamic ranges.

    To get information on named dynamic ranges and dynamic plotting, see these articles: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges and http://www.utteraccess.com/wiki/inde...namic_Charting.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: how to make line graph dynamic based on date ranges?

    I created 3 dynamic named ranges which are based on your Start/End Dates in I4 and J4

    For example XAxis

    =INDEX(Sheet1!$A$2:$A$300, MATCH(Sheet1!$I$4, Sheet1!$A$2:$A$300,0)):INDEX(Sheet1!$A$2:$A$300, MATCH(Sheet1!$J$4, Sheet1!$A$2:$A$300))

    I used 300 as upper limit of your expanding data. It consists of two INDEX Functions connected by a colon to create a range. The other two ranges (GoalY and PaceY) are very similar.

    Then you click on the chart, go to "Design" tab and "Select Data" Edit each series and axis. So, for example, click on the "Horizontal Categry Axis Labels EDIT button and RANGE
    = Sheet1!XAxis
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    Re: how to make line graph dynamic based on date ranges?

    Although both worked i went with the later as it was a bit cleaner to work with. thanks a bunch to both.

+ 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. Line Graph question - vetical line values at one fixed date
    By bohn in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-12-2015, 02:22 PM
  2. [SOLVED] Creating a Graph Using Dynamic Named Ranges
    By JasonMcQueen in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-03-2014, 10:23 AM
  3. [SOLVED] How to make dynamic graph based on drop down
    By Aaron_Tram in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-02-2013, 02:35 AM
  4. Replies: 1
    Last Post: 04-23-2013, 01:14 PM
  5. Make date ranges for graph (choose start and finish date)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2013, 09:13 AM
  6. [SOLVED] Dynamic line graph
    By AndrewBondarchuk in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-03-2013, 01:28 PM
  7. Graphs with different ranges (dynamic range graph)
    By Skybeau in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2010, 11:43 PM

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