Drop down excel graph

[jexcelboi]

Everyone,

I have ~10+ columns of data that I have made individual X Y scatter plots for but as my data set grows I would like to be able to select a drop down and only see that one chart. I have seen tutorials for basically a matrix where it looks up X and Y names and returns that 1 specific value but I am looking to plot thousands of data points in columns.

My column "titles" are simply 1, 2, 3, 4, etc. one tab is all of the X values and another tab all of the Y values I want to plot. Is there a way I can get this drop down feature to work so that I can just click "Test 12" and it shows that graph?

Thanks for any help, fairly new to dynamic properties in excel. I have attached an example sheet.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Pete_UK]

Use cell L1 for the drop-down, so select it and click on Data | Data Validation (twice), and in the Allow box choose List, then enter 12,13,14,15 in the Source box, then click OK. Use this drop-down to select the set of values that you wish to see.

Put the headings X Values and Y Values in L2 and M2, then these two formulae in the cells stated:

L3: =IF($L$1="","",INDEX($A3:$D3,MATCH($L$1,$A$2:$D$2,0)))

M3: =IF($L$1="","",INDEX($F3:$I3,MATCH($L$1,$F$2:$I$2,0)))

Copy these down as far as required. Verify that the values change when you select a different data set in L1.

Then you can use this table of values as the source data for your graph (I assume you know how to do that, from your opening post).

Hope this helps.

Pete

[MrShorty]

Here's how I did it in your sample file:

1) Enter 12 (or 13 or 14 or 15) in K1. Data Validation can easily be used to make this into a dropdown: https://support.office.com/en-us/art...6-eff3ce5f7249
2) Enter the text "x" and "y" into K2 and L2.
3) In K3, enter a lookup function to return the appropriate value from the x columns. I used something like =INDEX(A3:D3,MATCH($K$1,A$2:D$2,1)). Note the mix of relative and absolute references for easy copying.
4) In L3, enter a similar lookup function for the y columns.
5) Copy/fill K3:L3 down as far as needed.
6) Create scatter chart from from columns K and L
7) Because a scatter chart is not going to like the text "X" that you have towards the bottom of each column, I replaced those "X"s with =NA() errors. Excel's scatter charts ignore can ignore N/A errors, but text strings will mess up the scatter chart. (See essay here: https://support.office.com/en-us/art...6-eff3ce5f7249 ).

Will something like that work for you?

[jexcelboi]

Thank you both! This is working perfectly. I have a drop down now showing all of my charts. This has REALLY cleaned up my chart tab and this file continually grows with each passing test. This has made looking at the data much easier. I can simply make two of these drop downs now, select the various tests I want to compare and I should be able to get a great visual. I appreciate all of the feedback!

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete