+ Reply to Thread
Results 1 to 5 of 5

Auto populating multiple values based on two criteria based in combo boxes

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Auto populating multiple values based on two criteria based in combo boxes

    Hey all,

    I figure I'd take this chance to introduce myself and seek your assistance in a mind bending situation. I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Auto populating multiple values based on two criteria based in combo boxes

    Try this in F5,

    =VLOOKUP(INDEX(Data!$B$35:$B$61,Calculations!D$3),OFFSET(Data!E$1,MATCH(E5,Data!E:E,0)-1,,100,200),MATCH(INDEX(Data!$C$35:$C$53,Calculations!D$4),OFFSET(Data!E$1,MATCH(E5,Data!E:E,0),,1,200),0),0)

    then copy to other blocks.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    08-07-2013
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Auto populating multiple values based on two criteria based in combo boxes

    Thanks Haseeb! It works great. In trying to figure out your formula, I am wondering what sorts of modifications I am going to have to make as I add to the data tables the formula is referencing, i.e. more stores, more months, etc.. Thanks again for your help.

  4. #4
    Registered User
    Join Date
    05-19-2015
    Location
    Wesley Chapel, Florida
    MS-Off Ver
    2013
    Posts
    7

    Re: Auto populating multiple values based on two criteria based in combo boxes

    I came across your post while searching for another related issue - after reviewing the solution - here is a solution that may better suit your needs:
    Using name manager define Metric 1, Metric 2 and Metric 3

    In Cell F5 = Formula is =INDEX(Metric1,Calculations!$D$3,Calculations!$D$4), then change Metric1 to Metric2 for F7 and Metric3 for F9

    Using the Index function applies well in this case due to the structure of your data.

    Regards,

    Christopher Shay
    Last edited by ChristopherShay; 05-19-2015 at 10:52 PM. Reason: Updating information -

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Auto populating multiple values based on two criteria based in combo boxes

    Christopher's solution without name manager and using the headers in column E

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-26-2013, 07:36 AM
  2. Auto deletion of worksheets based on multiple criteria, and automatically returning values
    By iamrickdeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2013, 01:52 AM
  3. Replies: 5
    Last Post: 07-06-2012, 02:03 AM
  4. Calculation based on multiple Combo Boxes
    By emueller in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2010, 10:01 AM
  5. Replies: 4
    Last Post: 05-18-2010, 04:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1