Creating a Non-contiguous list in a drop-dowm dynamically.!

Creating a Non-contiguous list in a drop-dowm dynamically.!

I want a Dropdown which only selects all the Activities pertaining to whatever mentioned in the Column E.
For ex- I want all the Tasks assigned to Sarah , so I would be getting a choice amongst all the Tasks colored in Blue in a Dropdown, so If I were Sarah, then I would choose any Activity out of the 4 mentioned.
This dropdown would be based on the selected in the cells E2..

Originally Posted by davesexcel

Sorry I didn't notice the thread was in worksheet functions

Unhide columns G:H to see the formulas
select E2 to see the formula for your data validation

No problem Dave its the urge to help which is more important....
But unfortunately its not working the intended way.
When I tried selecting Sarah it just displays three options in the drop-down and
What I also want is to get different drop-downs in every Row so beneath E2 in E3 If I select Alice then I need all the tasks Assigned for..so on so forth...

Originally Posted by davesexcel

Did you try dragging the formulas farther down in column H ??

I am sorry I hadnt tried draggging It worked, however I would like to have it for the next cell E3 which does not work..

I would need to have different lists based on the selcted team members name in the adjacent cell E2 and E3..
So if I were to select Alice then it should display the corresponding activties whereas it still displays the activities for Sarah as $E$2 is locked..

So any solution for the same...

Originally Posted by all4excel

I am sorry I hadnt tried draggging It worked, however I would like to have it for the next cell E3 which does not work..

I would need to have different lists based on the selcted team members name in the adjacent cell E2 and E3..
So if I were to select Alice then it should display the corresponding activties whereas it still displays the activities for Sarah as $E$2 is locked..

So any solution for the same...

Hey Dave,

Did u try that, as Iknow thats very difficult but hope experts like you would be able to come up with some solution..
Multilookup in a Drop-down...

Originally Posted by davesexcel

Give me a break!
I showed you how to do it for the first one, are you not able to figure out how to do it for the next one?
I am not going to build your workbook for you.....

Dear Dave,

I am not underestimating your efforts at all.I am sorry if you felt that way..

The reason for seeking help from the Forum was to avoid any helper columns as they would be populated with the first Team Members Name..always

I have used Dynamic Lists in the past using Indirect,Offset,Defined names etc..However, this is a very different situation. I cannot define the Range as its non-contiguos as well as am trying to avoid storing it somewhere as I will have to assign different columns for different Team members..

So, in my case every Row would have a different list drop-down. that was not the only thing but the List has to be created dynamically without defining it....

I have tried different Logics which I have used in the Past but this seems to a bit difficult..

I was using this code to get Multilookup
[ =IF(ROWS(F$2:F2)<=COUNTIF($C$2:$C$21,List!$E$2),INDEX($B$2:$B$21,SMALL(IF($C$2:$C$21=List!$E$2,ROW($C$2:$C$21)-ROW($C$2)+1),ROWS(F$2:F2))),"") ]

This gives me the four Tasks for Sarah, but I am failing to use it in a drop-down as well as it does not change for anything in the next cell E3..

I am sorry for phrasing in this manner..

HTML Code: