Here's a tricky one!

[dantheram]

I'll try to explain this the best i can so bear with me!

Aim - to create a graph where the end user chooses the data to be displayed.

Progress - I have created the base graph and inserted a drop down menu, directly above containing the desired parameters. When the drop down is changed it triggers a vlookup to change the data in the cells where the graph reads from.

Problem - i can select parameter 1, 2 an 3 but 4 is designed to be ALL i.e. 1,2,3 combined. How can i get my vlookup to display data from 1,2 an 3 in the area where the graph reads from?

Hope that makes sense.

 

[theta]

Something like this...

Using row 1 as the series header (1,2,3)

-------1----2----3
Fred---x----x----x
Bill---x----x----x
John---x----x----x

So the formula for the first 'x' would be (then drag across and down)


'Y' = dropdown cell value (1-4)


=IF('Y'=4,values formula,IF(B$1<>'Y',#N/A,values formula))

 

[dantheram]

Well you'd need VBA to switch from a single series chart to a 3 series chart. Right-click the sheet tab, View Code, and copy this code in .... adjusting for your case of course:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$2" Then
    If Target.Value = 4 Then
         Me.ChartObjects(1).Chart.SetSourceData _
            Source:=Me.Range("A1:D4"), PlotBy:=xlColumns
    Else
         Me.ChartObjects(1).Chart.SetSourceData _
            Source:=Me.Range("A1:B4"), PlotBy:=xlColumns
         Me.ChartObjects(1).Chart.SeriesCollection(1).Formula = _
        "=SERIES('" & ThisWorkbook.Name & "'!myseriestitle,Sheet1!R2C1:R4C1,'" & ThisWorkbook.Name & "'!mysource,1)"
 
    End If
End If
End Sub

This is operating on a sheet with this layout:

Excel Workbook
	A	B	C	D	E	F	G
1		Val1	Val2	Val3			Series
2	Fred	22	24	55			1
3	Bill	33	25	56
4	John	44	26	58
Sheet10

... and these names:
myseriestitle =OFFSET(Sheet10!$B$1,0,Sheet10!$G$2-1,1,1)
mysource =OFFSET(Sheet10!$B$2,0,Sheet10!$G$2-1,3,1)

ok, now i'm going to look really silly. i've opened a new workbook and tried to set up the above example, with the exact same data in the exact same places.

I have clicked view code and entered the VBA but what do i do now?

 

[GlennUK]

Have you saved the workbook? What's the sheet name within the book where your chart and data is? Have you created the defined names for the series title and series data?

 

[dantheram]

Something like this...

Using row 1 as the series header (1,2,3)

-------1----2----3
Fred---x----x----x
Bill---x----x----x
John---x----x----x

So the formula for the first 'x' would be (then drag across and down)


'Y' = dropdown cell value (1-4)


=IF('Y'=4,values formula,IF(B$1<>'Y',#N/A,values formula))

this looks like it would work also but i can't follow how to plug it in. i cannot upload anything from work, sorry.

 

[dantheram]

Have you saved the workbook? What's the sheet name within the book where your chart and data is? Have you created the defined names for the series title and series data?

sorted, thanks very much for this Glenn!

 

[GlennUK]

That's great!