Creating a dashboard

Mike the Lord · 25. Januar 2019

Hi,

First post, so apologies if this is very or too basic.

I'm trying to create a reporting dashboard, a small sample of which is below.

I want to be able to change the fields in B2 to E2 in order to get the results in B5 to E6. I'm trying to use INDEX with MATCH (which might not be the best way I guess) but I get a #REF! error. This happens when I add a second MATCH criteria.

You may have noticed I am using a German keypad by the ";" instead of a comma but my formulas are set to English. Problem perhaps?

The example formula in C5 is =INDEX(F11:F22;MATCH(B2;B11:B22;0);MATCH(C2;C11:C22;0))

I've also tried, from searching on Google, to do simpler formulas with INDEX & MATCH but even when I follow the formula exactly, it doesn't work.

Can anyone help?

Mike

Mike the Lord

And breathe!!! Thank you so much Claus. This has worked. Most grateful.

Mike

Mike the Lord

Hi,

First post, so apologies if this is very or too basic.

I'm trying to create a reporting dashboard, a small sample of which is below.

I want to be able to change the fields in B2 to E2 in order to get the results in B5 to E6. I'm trying to use INDEX with MATCH (which might not be the best way I guess) but I get a #REF! error. This happens when I add a second MATCH criteria.

You may have noticed I am using a German keypad by the ";" instead of a comma but my formulas are set to English. Problem perhaps?

The example formula in C5 is =INDEX(F11:F22;MATCH(B2;B11:B22;0);MATCH(C2;C11:C22;0))

I've also tried, from searching on Google, to do simpler formulas with INDEX & MATCH but even when I follow the formula exactly, it doesn't work.

Can anyone help?

Mike

Guess a snippet wasn't the right Thing to send. Column B is "Market"

[table][tr][td]Market[/td][td]Brand[/td][td]Month[/td][td]Year[/td][td][td][td]Market [/td][td]Product[/td][td]Month[/td][td]Year[/td][/tr][tr][td]USA[/td][td]Pears[/td][td]January[/td][td]2018[/td][td][td][td]USA[/td][td]Apples[/td][td]January[/td][td]2017[/td][/tr][tr][td]Mike's Fruit Market[/td][td][td][td]Germany[/td][td]Pears[/td][td]February[/td][td]2018[/td][/tr][tr][td]Year[/td][td]Sales[/td][td]Revenue[/td][td]GP[/td][td][td][td]France[/td][td]Oranges[/td][td]March[/td][td][/tr][tr][td]2018[/td][td]#REF![/td][td] [/td][td] [/td][td][td][td]UK[/td][td]Bananas[/td][td]April[/td][td][/tr][tr][td]2017[/td][td] [/td][td] [/td][td] [/td][td][td][td][td][td]May[/td][td][/tr][tr][td]+/-[/td][td]#REF![/td][td] [/td][td] [/td][td][td][td][td][td]June[/td][td][/tr][tr][td]#REF![/td][td] [/td][td][td][td][td][td]July[/td][td][/tr][tr][td][td][td][td][td][td][td][td][td]August[/td][td][/tr][tr][td]Market[/td][td]Brand[/td][td]Month[/td][td]Year[/td][td]Sales[/td][td]Revenue[/td][td]GP[/td][td][td]September[/td][td][/tr][tr][td]USA[/td][td]Apples[/td][td]January[/td][td]2017[/td][td] 4.000 [/td][td] 2.000 [/td][td] 1.200 [/td][td][td]October[/td][td][/tr][tr][td]Germany[/td][td]Oranges[/td][td]February[/td][td]2018[/td][td] 4.111 [/td][td] 2.056 [/td][td] 1.233 [/td][td][td]November[/td][td][/tr][tr][td]France[/td][td]Pears[/td][td]April[/td][td]2018[/td][td] 3.350 [/td][td] 1.675 [/td][td] 1.005 [/td][td][td]December[/td][td][/tr][tr][td]Germany[/td][td]Apples[/td][td]February[/td][td]2017[/td][td] 2.005 [/td][td] 1.003 [/td][td] 602 [/td][td][td][td][/tr][tr][td]France[/td][td]Pears[/td][td]March[/td][td]2017[/td][td] 1.761 [/td][td] 881 [/td][td] 528 [/td][td][td][td][/tr][tr][td]UK[/td][td]Bananas[/td][td]January[/td][td]2017[/td][td] 1.070 [/td][td] 535 [/td][td] 321 [/td][td][td][td][/tr][tr][td]USA[/td][td]Apples[/td][td]April[/td][td]2018[/td][td] 412 [/td][td] 206 [/td][td] 124 [/td][td][td][td][/tr][tr][td]UK[/td][td]Bananas[/td][td]March[/td][td]2018[/td][td] 247 [/td][td] 123 [/td][td] 74 [/td][td][td][td][/tr][tr][td]France[/td][td]Oranges[/td][td]April[/td][td]2018[/td][td] 905 [/td][td] 453 [/td][td] 272 [/td][td][td][td][/tr][tr][td]UK[/td][td]Apples[/td][td]February[/td][td]2017[/td][td] 1.563 [/td][td] 782 [/td][td] 469 [/td][td][td][td][/tr][tr][td]USA[/td][td]Pears[/td][td]January[/td][td]2017[/td][td] 2.222 [/td][td] 1.111 [/td][td] 667 [/td][td][td][td][/tr][tr][td]Germany[/td][td]Apples[/td][td]February[/td][td]2018[/td][td] 2.880 [/td][td] 1.440 [/td][td] 864 [/td][td][td][td][/tr][/table]

Claus Busch

Hi Mike,

I guess that's a problem with the separators or the region settings.

Download the workbook from here:

https://1drv.ms/x/s!AqMiGBK2qniTgeEVfCRQpMU7LlW_ow

When you open it on your machine the formulas should be translated to your settings.

Claus

Claus Busch

Hallo,

oder einfacher in C5 mit:

=SUMIFS($F$11:$F$22,$B$11:$B$22,$B$2,$C$11:$C$22,$C$2,$E$11:$E$22,B5)

Claus

Claus Busch

Hallo,

du musst B2 und C2 in einem Schritt vergleichen. Probiere:

=INDEX(F11:F22,MATCH(B2&C2,B11:B22&C11:C22,0))

und schließe die Eingabe der Formel mit STRG+Shift+Enter ab (Array-Formel).

Claus

Hi,

willkommen im Windows Forum!

Creating a dashboard - Similar Threads - Creating dashboard	Forum	Datum
Unwanted spacing upon creating Outlook email with Python Unwanted spacing upon creating Outlook email with Python: Hello,at the end of a workflow I am automatically creating an outlook mail to inform selected co-workers.Unfortunately, when creating an HTML Body the Before and After spacing values seem to be...	Outlook.com	4. November 2024
Problem with creating a new Page using Ctrl + K in OneNote Problem with creating a new Page using Ctrl + K in OneNote: Hey there,since I've been struggling to figure out the issue for a while now, I've decided to just ask here. I'm at a loss and it's driving me up the wall.I use links in my pages a lot to keep...	Microsoft Office	26. Oktober 2024
Creating Sandbox in Developer Program Creating Sandbox in Developer Program: Hi everyone,I am trying to set up a sandbox in the Microsoft 365 Developer Program. I got told that I do not qualify for this in my Dashboard. Then, after redeeming a Visual Studio Pro license the...	Microsoft Office	2. Mai 2024
Creating a Custom Bibliography and Citation Creating a Custom Bibliography and Citation: Hello Guys and Girls,I used this Guide. And in there it says after saving it should show up in Style: drop down in the references tab but it wont in my casei have win11 and word2019 installed	Microsoft Office	10. April 2024
Mastering the Art of Blue Interior Design: Creating Timeless Elegance for Your Living Spaces Mastering the Art of Blue Interior Design: Creating Timeless Elegance for Your Living Spaces: Blue is a versatile color that can work wonders in various rooms. In the living room, it can create a cozy and inviting atmosphere. In the bedroom, it promotes restful sleep. We'll provide...	Allgemeines	21. September 2023
Outlook Event-Timezone out of Sync when creating Event on Calendar App iOS Outlook Event-Timezone out of Sync when creating Event on Calendar App iOS: Hi all, I have this weird bug where I create a new event on my phone using the native calendar app that syncs through my Exchange account and the timezone for the event always defaults to...	Outlook.com	13. September 2023
Site Usage reports across the entire SharePoint Intranet - creating a combined report Site Usage reports across the entire SharePoint Intranet - creating a combined report: Hi everyone,I need to analyze user activity across the entirety of SharePoint Intranet for my organization Communication sites. The metrics I need are most visited pages, most read news, popular...	Microsoft Office	30. Januar 2023

Creating a dashboard