Show multiple results from data sheet, based off 2 criteria selected in drop down menus

OzAlly

New Member
Joined
Jul 4, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
so I've got a spreadsheet set up for Trivia questions and responses. I am creating an easy database type of thing to look up and use.

The idea is that Based on a "Category" (Arts & culture, Brands & logos, Geography etc...). This is already a drop down list
Then there will be a "Criteria" (i.e. Geography has a list of all countries, European countries, African countries etc... )within that category. This is also a drop down list (dependant on the first list)
I also have a drop down list of the alphabet.

What i want to be able to do is to be able to select responses off the 'Criteria' list by letter of the Alphabet.
(e.g. Be able to select countries beginning with A) so then I get a result which has multiple answers i.e. Australia, Andorra, Austria etc...

I was able to get it functioning when i was looking at a single list - but when i have a full database looking at multiple lists of multiple responses I can't seem to get it to work. I can put the data on separate tabs - but i still have the same issue




1625408725675.png
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi OzAlly,

I'm not sure if I'm following your requirement completely, and your later version of Excel have have a better approach, but here's my Excel 2016 approach by building the LoV.

OzAlley.xlsx
BCDEFGHIJKLMN
1Built LoVCategoryCriteriaResponse
2AustraliaGeographyCountriesAustralia
3AustriaGeographyCountriesAustria
4CategoryCriteria (Starting with letter…)LetterResponseAngolaGeographyCountriesFrance
5GeographyCountriesA GeographyCountriesAngola
6 FoodCountriesFrance
7 FoodCountriesAustralia
8 ArtsPersonNed Kelly
9 ArtsDate1932
10 
11 
Sheet1
Cell Formulas
RangeFormula
I2:I11I2=IFERROR(INDEX($N$2:$N$9999,AGGREGATE(15,6,ROW($L$2:$L$9999)-ROW($L$1)/(($L$2:$L$9999=$B$5)*($M$2:$M$9999=$C$5)*(LEFT($N$2:$N$9999,1)=$D$5)),ROW()-ROW($I$1))),"")
Cells with Data Validation
CellAllowCriteria
E5List=OFFSET($I$2,,,COUNTIF($I$2:$I$99,"> "))
 
Upvote 0
Solution
Hi OzAlly,

I'm not sure if I'm following your requirement completely, and your later version of Excel have have a better approach, but here's my Excel 2016 approach by building the LoV.

OzAlley.xlsx
BCDEFGHIJKLMN
1Built LoVCategoryCriteriaResponse
2AustraliaGeographyCountriesAustralia
3AustriaGeographyCountriesAustria
4CategoryCriteria (Starting with letter…)LetterResponseAngolaGeographyCountriesFrance
5GeographyCountriesA GeographyCountriesAngola
6 FoodCountriesFrance
7 FoodCountriesAustralia
8 ArtsPersonNed Kelly
9 ArtsDate1932
10 
11 
Sheet1
Cell Formulas
RangeFormula
I2:I11I2=IFERROR(INDEX($N$2:$N$9999,AGGREGATE(15,6,ROW($L$2:$L$9999)-ROW($L$1)/(($L$2:$L$9999=$B$5)*($M$2:$M$9999=$C$5)*(LEFT($N$2:$N$9999,1)=$D$5)),ROW()-ROW($I$1))),"")
Cells with Data Validation
CellAllowCriteria
E5List=OFFSET($I$2,,,COUNTIF($I$2:$I$99,"> "))
Thank you so much for your help!
Not quite as elegant a solution as I was hoping - and i haven't figured out the best way to get ti to work with the data on another sheet - however it is working as I was hoping - so thank you very much!
I'll have a play around to see if i can shift the data to another tab (so the front page only have the question criteria)
Thank you!
 
Upvote 0
You're welcome!
...and maybe somebody with your later version of Excel will provide a simpler solution (FILTER and UNIQUE?) but until then here it is split over 2 sheets.

OzAlley.xlsx
ABCDEF
1Built LoVCategoryCriteriaResponse
2AustraliaGeographyCountriesAustralia
3AustriaGeographyCountriesAustria
4AngolaGeographyCountriesFrance
5 GeographyCountriesAngola
6 FoodCountriesFrance
7 FoodCountriesAustralia
8 ArtsPersonNed Kelly
9 ArtsDate1932
10 
11 
12 
Sheet2
Cell Formulas
RangeFormula
A2:A12A2=IFERROR(INDEX($F$2:$F$13,AGGREGATE(15,6,ROW($D$2:$D$13)-ROW($D$1)/(($D$2:$D$13=Sheet1!$B$5)*($E$2:$E$13=Sheet1!$C$5)*(LEFT($F$2:$F$13,1)=Sheet1!$D$5)),ROW()-ROW($A$1))),"")


OzAlley.xlsx
ABCDEF
1
2
3
4CategoryCriteria (Starting with letter…)LetterResponse
5GeographyCountriesA
6
Sheet1
Cells with Data Validation
CellAllowCriteria
E5List=OFFSET(Sheet2!$A$2,,,COUNTIF(Sheet2!$A$2:$A$99,"> "))
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top