Dynamic List

**tsevereid** · 11-30-2010, 04:18 PM

Hi All,

I'm trying to use a dynamic list and after reading the other solved threads on this same topic I am still having no luck with my problem. It's probably an easy fix that I'm overlooking but I would really appreciate some help.

I'm working with a list that changes based on how what employee works at each service center. I am currently using a data validation list to change the service center (Cell A2 in the example) and I want the employees of that service center only to populate in the next drop down (B2) without returning #N/A. I am using 9 as the number of employees (column H) in the employees tab as 9 is the maximum amount of employee at any service center.

I have tried =offset(countif()) with no luck. I admittedly am not too familiar with these functions so it could easily be an EBKAC error. An example of my problem is attached

Thanks,
T

**NBVC** · 11-30-2010, 04:26 PM

If you name the range Employees!$A$1:$A$24 as EmpList

then you can apply data validation list formula as:

=OFFSET(EmpList,MATCH(A2,EmpList,0),3,COUNTIF(EmpList,A2),1)

**tsevereid** · 11-30-2010, 04:48 PM

Thanks for the prompt response. That answered my question 99% of the way. When I use that validation it pulls the employee one sport down from the one that I am looking for. So for service center 1, it returns emp2, emp3 in the next drop down rather than emp1, emp2.

Thanks again

**NBVC** · 11-30-2010, 04:51 PM

You are correct.

Change validation formula to:

=OFFSET(EmpList,MATCH(A2,EmpList,0)-1,3,COUNTIF(EmpList,A2),1)

**tsevereid** · 11-30-2010, 04:55 PM

Thanks a lot.

Do you have any reading recommendations for formulas of this nature? Most of the courses/books that I have been exposed to have not covered this topic.

**NBVC** · 11-30-2010, 05:01 PM

there are many online places you can go for these formulas... they are found usually in forums like this, as responses to questions like yours. No book or one tuturial site can cover all the variations.. it takes a bit of experience and creativity sometimes to come up with working formulas and then there are always other totally different approaches too...

Try nosing around this site.

Also google for Contextures, Peltier, Walkenback. They will lead you to good reads.

There are also many links posted here:

http://www.excelforum.com/excel-gene...additions.html

and

http://www.excelforum.com/excel-gene...additions.html