+ Reply to Thread
Results 1 to 7 of 7

Dynamic Charting with START and END selections

  1. 06-01-2019, 06:35 PM #1
    Space_Crime
    Space_Crime is offline
    Registered User
    Join Date
    06-05-2016
    Location
    Canada
    MS-Off Ver
    Office 14
    Posts
    8

    Dynamic Charting with START and END selections

    Hello All,

    I have a sample file attached with a simple single column monthly data and added data validation to select the start and end month so I can chart the selected range.
    I have created ranged names for the data but unable to get the chart working properly. I am stuck at trying to find the data series in charting working as it is frustrating.


    Thanks in advance.

    Jay
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-02-2019, 01:56 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Dynamic Charting with START and END selections

    Welcome to the forum!

    What I do for this sort of thing is create a dynamic table containing the required data, then use this as the chart's source. I then hide the dynamic table under the chart object.

    I have created the following table in the attachment:

    Excel 2016 (Windows) 32 bit
    G
    H
    4
    Month
    Value
    5
    		 Feb
    9
    6
    		 Mar
    8
    7
    		 Apr
    7
    8
    		 May
    10
    9
    		 Jun
    7
    10
    		 Jul
    10
    11
    		 Aug
    5
    12
    		 Sep
    4
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    G
    H
    5
    		 =IF(OR(G4="",G4=E$3),"",INDEX($A$2:$A$13,MATCH(E$2,$A$2:$A$13,0)+ROWS(G$5:G5)-1))
    =IFERROR(VLOOKUP(G5,$A$2:$B$13,2,0),"")
    Sheet: Sheet1

    Change the parameters in your coloured cells to see the chart change.
    Attached Files Attached Files
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 06-02-2019, 02:05 PM #3
    Space_Crime
    Space_Crime is offline
    Registered User
    Join Date
    06-05-2016
    Location
    Canada
    MS-Off Ver
    Office 14
    Posts
    8

    Re: Dynamic Charting with START and END selections

    Hello Ali,

    Thanks for this approach, however when selecting some parameters, the chart is not showing the desired range. For ex. Jan-Sep, Apr-Dec, or Feb-Oct.
    On your charting source table, the output works flawlessly but is in the actual chart some of those ranges don't appear correctly.

    Jay.
    Reply With QuoteRegister To Reply
  4. 06-02-2019, 04:23 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Dynamic Charting with START and END selections

    OK - I will have another look in the morning. Probably a simple fix.

    Check the data that has been selected for the chart - it’s probably just a case of making sure that it’s 13 rows deep.
    Reply With QuoteRegister To Reply
  5. 06-02-2019, 04:39 PM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Dynamic Charting with START and END selections

    Yes - that’s all you need to do. Right-click the chart and choose Select Data. In the dialog change the source range at the top to G5:H16. Now the chart will work correctly.
    Reply With QuoteRegister To Reply
  6. 06-02-2019, 06:55 PM #6
    Space_Crime
    Space_Crime is offline
    Registered User
    Join Date
    06-05-2016
    Location
    Canada
    MS-Off Ver
    Office 14
    Posts
    8

    Re: Dynamic Charting with START and END selections

    Yes, that fixed it. Perfect, thanks.
    Reply With QuoteRegister To Reply
  7. 06-02-2019, 11:29 PM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Dynamic Charting with START and END selections

    No problem.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filling in charting dates with start date and # of months
    By etaN in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2016, 10:34 AM
  2. [SOLVED] Dynamic Vlookup based on selections
    By pdalal in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-04-2014, 08:14 PM
  3. Charting multiple start and end times day after day
    By David Lawrence in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2013, 06:29 AM
  4. [SOLVED] Dynamic charting based on two drop down selections - help
    By TalResha in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 01-07-2011, 06:52 AM
  5. Charting multiple start/finish date/time stamps
    By kerrie92 in forum Excel General
    Replies: 0
    Last Post: 05-17-2010, 01:49 PM
  6. select dynamic range with dynamic start point
    By Juli in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2005, 08:05 PM
  7. Dynamic charting
    By tom300181 in forum Excel General
    Replies: 2
    Last Post: 07-26-2005, 09:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1