# Thread: Question : Multiple Criteria for Data Validation

1. ## Question : Multiple Criteria for Data Validation

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

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

Thank you in advance.

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

#### Posting Permissions

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