Question : Multiple Criteria for Data Validation

rpc86

New member
Joined
Apr 11, 2014
Messages
1
Reaction score
0
Points
0
Hi,

I'm new on this forum. I have a problem in getting rows from a multiple criteria. Below is my source table:

Sheet 1
Resource
Region
Name
DBA
Europe
John
DBA
Asia
David
QA
Africa
Paul
DBA
Asia
William
QA
Asia
Henry
QA
Europe
Mirla











Sheet 2:

I have 3 cells A, B and C.

A is for Resource and B is for Region. If I select DBA in cell A and Asia in cell B, then David and William will be added on my dropdown list for cell C. Someting like this:


A
B
C
Resource
Region
Name
<dropdown>
<dropdown>
<dropdown>






Please help me in using OFFSET and MATCH functions for this.

Thank you in advance.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
In Sheet 1, create another column, say column K to list the names for data validation population, and insert formula in K2:

=IFERROR(INDEX($C$2:$C$7,SMALL(IF($A$2:$A$7=Sheet2!$A$2,IF($B$2:$B$7=Sheet2!$B$2,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($K$2:$K2))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER.
Then copy down as far as the original Name list.

Now name this column, Name. Do this by select column K and typing the word Name in the Name Box just to the left of the formula bar.

Now for the Data Validation in Sheet2. Use List and formula:

=OFFSET(Name,1,0,COUNTIF(Name,"?*")-1,1)
 
Top