Solved: Graph populated by Dropdown

[Hoopsah]

Hi

does anyone know ho to create a graph that changes its details depending on the choice made from a dropdown menu

I have a series of data and I want to let the user choose a specific region and a specific day of the week from dropdown menus and the graph reflects this choice

Thanks for any help

Hoopsah

[mdmackillop]

Can you post a sample data file and typical graph?

[Hoopsah]

Hi MD

I have attached a copy of the sort of data that we have. I am hoping to get just a standard bar graph showing a count total.

I would like to have 2 dropdowns asking: Month & Regions and only show the details for those. Along the bottom I would like: Mondays, Tuesdays etc. and the user can then pick February & Ness and it would show the details for all the Standard connections made on a monday next to all the non-standards etc.

Hope this is clear - I am still at the headscratching stage at the moment

Cheers MD

Gerry

[mdmackillop]

Hi Gerry
I can't see where the Months come into this.

[Hoopsah]

Ooops!

Sorry I just cut and pasted some data - I have attached the actual sheet this time.

Cheers

[ProteanBeing]

If the data could be stored on a seperate tab for each selected item then this code will work.

[vba]prmSelection = Range("B2").Value + 1
ActiveSheet.ChartObjects("chartLastTen").Activate
ActiveChart.SetSourceData Source:=Sheets(prmSelection).Range("B1:B11")
ActiveChart.SeriesCollection(1).XValues = Sheets(prmSelection).Range("A2:A11")

[/vba]
prmSelection is based on the selection in a combo box that is on the first sheet (interface) and adds one to make the second sheet the first data sheet. "ChartLastTen" is my named chart on the first (interface) sheet. Since the data is uniform, the code simply references the correct sheet.
If the format you have has to be that way. I would still put each group on a seperate tab and create another sheet linked to this data for your report.

[Bob Phillips]

Gerry,

It would be better if you could re-structure the data, like this perhaps
February Monday Ness Std Connection 7
February Monday Ness Non Std Connection 1
February Monday Ness Sewer Connection 1
February Monday Ness Water Facilitation 3
February Monday Ness Sewer Facilitation 0
February Monday Don Std Connection 7
February Monday Don Non Std Connection 0
February Monday Don Sewer Connection 2
February Monday Don Water Facilitation 1
February Monday Don Sewer Facilitation 0
...
...
February Tuesday Ness Std Connection 3
February Tuesday Ness Non Std Connection 0
February Tuesday Ness Sewer Connection 6
February Tuesday Ness Water Facilitation 3
February Tuesday Ness Sewer Facilitation 4
...
...
March Monday Ness Std Connection 1
March Monday Ness Non Std Connection 2
March Monday Ness Sewer Connection 3
March Monday Ness Water Facilitation 4
March Monday Ness Sewer Facilitation 5

it will make it simpler to build the graphs then as we could assign named ranges and use these

[Hoopsah]

Thanks for your input guys.

I'll away and do a bit more headscratching and either come back and ask again or let you know how I got on

Cheers again

Gerry

[Hoopsah]

Ok,

so far I have changed the data layout and created a graph for the Monday only.

So, anyone know how I would add a dropdown to let the user choose Tuesday, Wednesday.........

[Bob Phillips]

A simple pivot chart

[Hoopsah]

Aw Man!!

I'm really sorry Bob, as soon as I read your post "a simple pivot chart" - before I even opened it it dawned on me that it should be a pivot chart.

You have shown me how to do a proper pivot chart before and believe it or not I actually use it every week.

Sorry for not realising sooner - but thanks once again for you help

Take care Bob

Gerry