• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Break out dashboard |Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Break out dashboard |Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsBreak out dashboard
sp_PrintTopic sp_TopicIcon
Break out dashboard
Avatar
Jay Bee

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 14, 2019
sp_UserOfflineSmall Offline
1
January 14, 2019 - 2:37 am
sp_Permalink sp_Print

I am currently creating a breakout dashboard based on a few metrics that aggregate into a main dash where the metrics update dynamically based on my data validation drop down.  There was one number where I needed to divide numbers based on multiple criteria similar to a sumIFS statement but I realized there is no formula that does this seamlessly.  As a way around this I divided all the numbers in one column then created a formula to return that number based on two criteria using an INDEX match formula, only problem is that the data is on a different tab.  I did end up getting my desired result but I am not sure why it worked.  When I put the 1 in front of the match formula it makes it dynamic returning the result I want based on name and date but when I take the 1 out it only gives me the first row.  No where does any match formula say by adding the 1 it makes it dynamic.  I have deducted that is what the formula is doing but I was wondering if I could get more clarity if I am correct in this line of thinking.  Please see below for full formula 

=ArrayFormula(index(A2:J,MATCH(1,(B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1),0),6)

 

* Right after the match(1 is the one that is question when I take it out the forumla does not update and when I put in 1 it does.  I know this has nothing to do with the exact match because those belong at the end of the forumla.  When I click on the 1 it highlights search key "The value to search for. For example, 42, "Cats", or I24." But this can not be the case because the following part of the formula is technically my search key (B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1)

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
January 15, 2019 - 4:35 am
sp_Permalink sp_Print

Hello Jay,

I think you will find the answer in this blog post.
https://www.myonlinetraininghu.....in-a-range

Avatar
Jay Bee

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 14, 2019
sp_UserOfflineSmall Offline
3
January 24, 2019 - 6:45 am
sp_Permalink sp_Print sp_EditHistory

Hi so not quite but I believe we are on the right track of using a dynamic index match based on multiple conditions in my case name and date.  I have a data validation of week and sales rep and I want to return a value when both conditions are met based on a huge list of source data.  Like stated above this formula does the trick =ArrayFormula(index(A2:J,MATCH(1,(B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1),0),6) . BUT only if I add the one after MATCH(1,  if I take the one out it is static.  From the article you gave me it used True but only gives the first or last value.  I would want mine to be truly dynamic.  AKA return this value any time this week and this name is selected on my drop down data validation list.  Example John Smith week of 11/21/2018 return his sales and then if I change the week or the name it will then find that cell connected to that name and date and return it.  My question is why does this 1 make it dynamic no where in that article or the formula does it say add that one to make it update based on my two conditions week and name. If you could expand on this I would greatly appreciate it 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 613
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
January 24, 2019 - 8:08 pm
sp_Permalink sp_Print

First off, it looks like you're using Google sheets, not Excel?

 

The 1 doesn't make the formula dynamic, it's the value that is being looked up. This part of the formula:

(B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1)

produces an array of 1 and 0 values (1 where both conditions are met, 0 if not), so your working formula finds the first matching value. You could also use a FILTER formula in Sheets.

Avatar
Jay Bee

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 14, 2019
sp_UserOfflineSmall Offline
5
January 31, 2019 - 5:11 am
sp_Permalink sp_Print sp_EditHistory

Awesome that I think answers my question I wanted it to meet two conditions NAME and DATE which is on my breakout dash and is subject to change based on the data validation drop down list.  EXAMPLE John Smith and week ok 12/01/2018 Return average sales for this week from this cell when both conditions are met.  So I THINK what you are saying is by having the 1 there it needs both conditions to be be true and if it is 0 only one?  I very much understand the part of the formula (B2:B='Dashboard '!$A$1)*(A2:A='Dashboard '!$B$1) It was just no where under any learning material did it have that distinction.  Thank you very much I will go forward assuming this is true. Next I will have a look up for the company as a whole so I only need one condition to be true if what you say is correct the same formula should work with a 0 not a 1 this time.  

Again thank you for the clarification and if there is an either way to index match based on multiple criteria that is subject to change (I.E) choosing name and date from a drop down list and aggregating an average based on those criteria to a dashboard please let me know!!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Carlos Ferreira, Chandler Davis, baber Tufail
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.