Different granularity for dashboard

[waimea]

Hi, I am trying to create a dashboard and I have 2 activex comboboxes.

Combobox1 is on a national level, e.g Sweden, Norway, Finland etc.

Combobox2 is on a city level, e.g Stockholm, Olso, Helsinki.


So when I select "All countries" in combobox1 it shows data and graphs for all countries.

When I select a country I want to show data and graphs for that country. (using the same graphs and spreadsheet space)

Also, when I select a city I want to show data and graphs for that country. (using the same graphs and spreadsheet space)

In my spreadsheet I have 22 countries and 100+ cities.


I have tried with if statements but I can't get it to work?

Switch function? Nested if statements?

All suggestions are welcome?

[waimea]

I want to use VLOOKUP/SUMIFS/COUNTIFS.

But when I use
=SUMIFS(amount; criteria_range1; combobox1, criteria_range2: combobox2) I only get data on the city level.

I want to have one SUMIFS for the national level and another SUMIFS for the city level.

How can I do this? 22 different nested IFS?

[Paul_Hossler]

Attach a small WB with the comboboxes and some data -- it'll make it easier to see

[waimea]

Hi Paul,

thank you for your reply!

I did a quick mockup, I want to show the info for combobox1 choiche and combobox2 choice in the same cell.

[waimea]

I though about hiding one shape on combobox2 change or click and showing another shape with another link in it?

[Paul_Hossler]

I want to display the data for norway and oslo in the same box

What do you mean?

There are 3 boxes: All Countries, Country, City

[waimea]

Hi Paul,

thank you for your reply.

I was looking at my workbook and not at my mockup.

Say I have data in cell B2, when I select "All" I want to display the amount in cell B2.

Then I drill down one level to say country "Sweden" I want to display the amount for Sweden in cell B2.

Finally, I drill down one more level to city "Stockholm" and then I want to display the amount for Stockholm in cell B2.

Is this clearer?

[waimea]

Did my additional info give you a better understanding?

I apoligize if I am explaining it poorly.

[Paul_Hossler]

No, it was fine

Just got side tracked with the grandkids

[waimea]

Are you teaching the grandkids Excel?

[Paul_Hossler]

Are you teaching the grandkids Excel?

:rotflmao::rotflmao::rotflmao:

No, just trying to keep them from fighting

As a suggestion, it might be easier to use a pivot table with some slicers

1. Don't need to keep List worksheet up to date
2. More options, more powerful options, more and better formatting options
3. No VBA required, unless you want to get fancy

You can tie in to the Workbook_SheetPivotTableUpdate event for post-selection formatting, etc.



Option Explicit


Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)


With Target
.TableRange1.ColumnWidth = 15
End With
End Sub




Look at the sample attachment

[waimea]

Hi Paul,

thank you for your reply and your workbook!

I see what you did with the slicers.

Are you saying that I should link a cell in the pivottable to the "box" that I want to display data in?

[Paul_Hossler]

Actually I was thinking that for a dashboard, the pivot table with slicers would be all you needed

You can use the slicers in a different worksheet and GETPIVOTTABLEDATA to retrieve data from the PT and put in a cell

Small incomplete sample attached, personally I think the PT with Slicers is an easier approach

Do you really need to put the numbers into a worksheet cell?

[waimea]

Hi Paul,

thank you for your reply! I have to read up on pivottables!

I am trying to get different data in the same cell/textbox/shape.

Could it be done with nested if formulas? Switch formula?

[Paul_Hossler]

Well ...

In E25 on Sheet1


="The population of "&$J$16&" in "&$E$16&" is "&TEXT(GETPIVOTDATA("Amt",Dashboard!$B$2,"Country",$E$16,"City",$J$16),"#,##0")

Returns "The population of Ådense in Denmark is 12,000"

Although it returns #REF if (for example) City = "All Data"

Might be possible to come up with a function that handles the different cases

[waimea]

Hi Paul,

thank you again for your reply!

I have been thinking about this and what if I would use .formula in a switch case statement?

[Paul_Hossler]

I started with a list of If/ElseIf/Else/End If statements since

Region can be 'specific' or 'All Items'
Country can be 'specific' or 'All Items'
City can be 'specific' or 'All Items'

That makes 8 combinations and the GETPIVOTTABLEDATA statement seems to be a little fussy

The advantage of just the PT with slicers as I saw it is simplicity just using it as Excel provides

Something more complicated like putting data onto a worksheet might require another approach

Let me think on it

Maybe someone else will come up with a better idea

[waimea]

I removed region so there are fewer combinations. I also removed all cities.

Country can be "all" OR "specific"
City can be specific"

I am thinking that I use select case statement for say. country = Sweden.

And I then use a specifc formula for country = Sweden.




Private Sub DashboardCountry_Change()Me.DashboardCity = ""


Select Case Me.DashboardCountry


Case "All"
Me.DashboardCity.ListFillRange = "All"

''''''''''''''''''''''''''''''''''''' Here specific code starts


Range("B12").Formula = "=SUM(B14:B15)"

Case "Sweden"
Me.DashboardCity.ListFillRange = "Sweden"

''''''''''''''''''''''''''''''''''''' Here specific code starts

Range("B12").Formula = "=SUM(B14:B15)" ' SUMIFS FORMULA FOR SWEDEN


End Select

End Sub

[waimea]

Since I am trying to use shapes, I googled and found that I can change for formula in a shape with


ActiveSheet.TextBoxes("DashboardAmount).Formula = "=WHAEVER FORMULA I SHOULD USE"



I am not sure however, how to switch the shape from combobox1 (country) amount to combobox2 (city) amount?

TextBox "DashboardAmount" should display "All" or "specific" country..

TextBox "DashboardAmount" should display "specific" city.

Any ideas?

[waimea]

Perhaps there is a way to check if Combobox1 and/or Combobox2 has been chosen and then use indirect or some kind of lookup based on either combobox1 or combobox2??

[Paul_Hossler]

Going back to the non-PT approach, I added simple formulas to your original example.

There's two comments marked in yellow that I don't understand. If you add your expected results, I'll take a look again

25226

[waimea]

Hi again Paul,

thank you for your help! You are very kind!


What I am trying to do is to get ALL output in the "ALL COUNTRY AMOUNT" box. So ignore the "COUNTRY AMOUNT" box and the "CITY AMOUNT" box.


Step 1. "All" countries is selected and output is in "ALL COUNTRY AMOUNT" box.

Step 2. I select a country. Say Norway and the amount for Norway is outputted into "ALL COUNTRY AMOUNT" box.

Step 3. I select a city in Norway, say Oslo and the amount for Oslo is outputted into "ALL COUNTRY AMOUNT" box.



As of now I have separete boxes for Country and City but I would like to have the output in the same box.

[Paul_Hossler]

I'd say a user defined function would work then




Option Explicit


Const colCity As Long = 1
Const colAmt As Long = 2
Const colPop As Long = 3
Const colCountry As Long = 4
Const colRegion As Long = 5


Function ShowData(Country As String, City As String) As Double

'no country, no city
If Country = "All" Then
ShowData = Application.WorksheetFunction.Sum(Worksheets("Data").Columns(colAmt))

'country, no city
ElseIf Len(City) = 0 Then
ShowData = Application.WorksheetFunction.SumIf(Worksheets("Data").Columns(colCountry), Country, Worksheets("Data").Columns(colAmt))

'country and city
ElseIf Len(Country) > 0 And Len(City) > 0 Then
ShowData = Application.WorksheetFunction.SumIf(Worksheets("data").Columns(colCity), City, Worksheets("Data").Columns(colAmt))

End If
End Function

[waimea]

Hi Paul,

this is exactly what I wanted!

Thank you very much for your help. I want to understand your code and your UDF so that I can append it.

I understand that you use the named ranges as inputs and that you declare the column numbers as variables.

Could you explain the code a bit more?

Ps.

Do you have any good booktips on VBA?

[Paul_Hossler]

The 'Country' and 'City' in the function definition are String calling parameters, local to the function. It's coincidence that they're named the same as your ranges. They could be X and Y, but meaningful variable names are less error prone

The columns are Constants, in that they get a value that can't be changed. I like to use that to avoid so-called 'magic numbers' = "Where did 3 come from?" since colPop is more explanatory

There are 3 possible situations
'no country, no city
'country, no city
'country and city

So the If/Then just tests (BTW, it's important to test in the right sequence)

[waimea]

Hi Paul,

thank you for your explanation! I will experiment with your UDF tomorrow!

I appreciate your help and for sticking with the subject! :)

[waimea]

Hi Paul,

just wanted to say that I got your UDF working really well. Thank you again!

[Paul_Hossler]

Glad you got it working the way you want

You can mark your question SOLVED (#3 in my sig)

[waimea]

Hi Paul,

I marked the thread as solved and thank you for your help!