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