Graph With a Chart

[peterules83]

Hello

First Post. Love the forum

I have a data validation list where the user can choose up to 10 items from a drop down. If they only want to choose 2 items, then can as well. A graph is linked to a vlookup which detects what item the user has chosen and graphs the data for the last 5 years.

My problem is that all the 10 items show up on the graph. If someone only enters in two items, there remains 8 blank series on the legend in the graph. Is there a way to only graph the number of items that the user wants and not display all 10 items on the graph?

So if I choose 4 items, only 4 will show up on the graph? Not the remaining 6 items that weren't selected?

[ChemistB]

Can you upload an example of how your data is laid out and operates (go Advanced>Manage Attachments)?

[peterules83]

Hi ChemistB. Thanks for looking into this for me. Really REALLY appreciate your expertise

I uploaded my excel sheet to look at. I probably did not do this in the most efficient way so I will explain

A1-A7 - that is my list for the data validation.
D1-D7 - this is a drop down menu so someone can enter A-G in. You can have all 7 (A-G) or just 2 (A & B)
I1-I8 (Green Chart) - This is the raw data
I11-I18 (Pink) This is the vlookup using the raw data. Any data not entered comes in as #N/A
I20-27 (Yellow) - This is just a prettier version of the V lookup table. I shows that if you left Item 5 blank, it will leave the data in table 5 blank.

My Main Challenege is the graph and table showing the blank line an blank 0's across the board. I don't want those to show.

Cheers

[ChemistB]

Okay, after a lot of deadends, I decided hiding the rows that don't have data is the easiest way to go.

First, You don't need that second data table. I used this formula in your yellow table which can be dragged across and down

=IF(ISNUMBER(MATCH($I21,$D$1:$D$7,0)),VLOOKUP($I21,$I$2:$M$8,COLUMN(B1),FALSE),"")

Then it's necessary to write an event driven macro to hide rows that aren't being used.
Copy the code here into your spreadsheet. To do that;
1. Right click on the sheet tab and "View Code"
2. Paste the code into the large text box that is in the window that opens up (that window is the VBA Editor)
3. Close that window

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, DataRng As Range
Set DataRng = Range("I21:I27")   
Application.ScreenUpdating = False
If Not Intersect(Target, Range("D1:D7")) Is Nothing Then

    For Each Cell In DataRng
        If Cell.Value = "" Then
            Cell.EntireRow.Hidden = True
        ElseIf Cell.Value <> "" Then
            Cell.EntireRow.Hidden = False
        End If
    Next Cell
End If
Application.ScreenUpdating = True
    
End Sub

Questions?