+ Reply to Thread
Results 1 to 6 of 6

Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop down.

  1. #1
    Registered User
    Join Date
    04-06-2021
    Location
    Norway
    MS-Off Ver
    Several older verisons, but mostly 365
    Posts
    69

    Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop down.

    Hi !
    I have a line chart where i have 6 data sets, i wish to create a drop down list where i want to make 4 combinations of these 6 data sets. (in other words get 2 line plots up in the chart with one drop down selection)

    Is this possible ?

    Regards:
    Jan Ove

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop dow

    You might be able to do what you want using a pivot table.
    It might be easier to understand what you want if we could see a sample of the Excel file (instructions are in the banner at the top of the page).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    04-06-2021
    Location
    Norway
    MS-Off Ver
    Several older verisons, but mostly 365
    Posts
    69

    Re: Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop dow

    Hi !
    Thank you for your reply.

    Added a example with description, hope you are able to understand what i am aiming for..

    Regards:
    Jan Ove
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop dow

    Perhaps this will be of some help.
    The drop down in cell H1 has the "Actual" options (trip in and trip out)
    The drop down in cell H3 has the "Theoretical" options as listed in J1:J4
    Cell A100 is populated using: =IF(H1="ATI","Actual Trip In","Actual Trip Out")
    Note that cell B100 could be similarly populated using the "Theoretical" options.
    A101:A120 are populated using: =IFERROR(IF(H$1="ATI",1/(1/A1),AGGREGATE(15,6,B$1:B$20/(B$1:B$20<>""),ROWS(A$101:A101))),NA())
    B101:B120 are populated using: =INDEX(K$1:AD$4,MATCH(H$3,J$1:J$4,0),ROWS(B$101:B101))
    The chart in S9:Y22 is populated using the data in A100:B120
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-06-2021
    Location
    Norway
    MS-Off Ver
    Several older verisons, but mostly 365
    Posts
    69

    Re: Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop dow

    Hi again !

    I am wanting to have 2 variants of actual trip in one that compares theoretical volume CEI, and one that compares theoretical OEI.
    Same for actual trip out, actual trip out compared to OEO, and actual trip out compared to CEO

    But that might not be possible to combine 2 data sets in one drop down list ?

    Regards:
    Jan Ove

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Line chart, create drop down list where 2 data sets (graphs) is combined in 1 drop dow

    Let's see if this helps.
    Put the pairs of options in cells D100:D103 and have the source of the data validation for cell H1 reference that range.
    Cell H3 can be populated using: =RIGHT(H1,4)
    [Optional] The font could be hidden by changing it to white for aesthetic purposes
    Cell A100 is populated using: =IF(LEFT(H1,3)="ATI","Actual Trip In","Actual Trip Out")
    Cells A101:A120 are populated using: =IFERROR(IF(LEFT(H$1,3)="ATI",1/(1/A1),AGGREGATE(15,6,B$1:B$20/(B$1:B$20<>""),ROWS(A$101:A101))),NA())
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. Mixing dynamic excel drop down list variables in line chart
    By ehhezron in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-15-2020, 02:46 PM
  2. Replies: 4
    Last Post: 01-04-2019, 12:29 PM
  3. Using Drop Down Boxes to Create Dynamic Line Chart
    By Branbran10 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-25-2015, 04:30 PM
  4. Several Line Graphs w/ Drop Down List
    By Rosco88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2013, 04:40 AM
  5. Replies: 15
    Last Post: 07-08-2013, 11:48 AM
  6. How to create a drop down list based on the choices of another drop down list?
    By Happy jain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2013, 01:54 AM
  7. [SOLVED] Combine 2 data sets into one list based on user selection in drop downs.
    By Sassy13 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-01-2013, 01:46 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