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

1. ## 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. ## 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.

3. ## 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

4. ## 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.

5. ## 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. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

#### 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