I can only think to do it w/ macros attached to the drop downs:
1) in your graph go into "format axis" and on "number" tab, check "linked to source". Then add a macro like this to your date/week drop down:
If Range("D30") = 1 Then
Range("B36:B60").Select
Selection.NumberFormat = "m/d/y"
ElseIf Range("D30") = 2 Then
Range("B36:B60").Select
Selection.NumberFormat = "#"
End If
2) Add a macro like this to your city drop down:
ActiveSheet.ChartObjects("Chart 3").Activate
If Range("B30") = 1 Then
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B36:C60"), PlotBy _
:=xlColumns
ElseIf Range("B30") = 2 Then
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B36:C60"), PlotBy _
:=xlColumns
ElseIf Range("B30") = 3 Then
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B36:C60"), PlotBy _
:=xlColumns
ElseIf Range("B30") = 4 Then
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B36:C53"), PlotBy _
:=xlColumns
ElseIf Range("B30") = 5 Then
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B36:C52"), PlotBy _
:=xlColumns
End If
Note that you could make the ranges dynamic but that's a bit more complicated. You can probably search on methods for setting dynamic ranges in macros if you want to go that route.
Also note that certain elements will change if you implement this in another spreadsheet (ie, the chart won't always be "Chart 3", etc).
(colors added for emphasis only)
Bookmarks