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

1. 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.

2. 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.

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. 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

5. 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:
`Please Login or Register  to view this content.`

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

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