Drop Down Lists

Is this the sort of thing you wanted?

It is dyanmic, as you add names to the list they are automatically availble in the drop downs.

happy to explain.

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Ok. More explanation is needed. You haven't really told me much. I have set up a basic drop down for agent name in I2 to I6. What do you expect to appear and where do you expect it to appear?

Next Q. In your real data , how many agents are there?

In your real data, how many ROWS of these two-tier dropdowns do you need MAXIMUM?

There are a couple of ways forward, but the practicality of each/either/both depends on the answers to these Qs.

How is Excel supposed to know which tasks belong to which person?

I am getting a bad feeling about this one... If this is not what you want, I will have one further Q... Q2, re-phrased, as you didn't quite answer the Q that I had asked.

I am out for 2 hours. here is a final version

Column A automatically alphabetically sorts the unique names from column C. Add a few more agents and tasks.

The range in the formula in column K will aso automatically adjust as new agent/tasks are added.

Check it over and add new agents/tasks in C&D. I will explain when I get back.

Formulae generating the lists are set to work for 1000 rows.

Behind the scenes there are two Named Ranges. (CTRL-F3 to view). These dynamically adjust the ranges in column C - the list of all the agents, which occur several times) which is used by the formula that produces the data in column A. The other one is in column A, that is used by the data validation formula in column k.

All_Agents
=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,COUNTA(Sheet1!$C$2:$C$1000))
This selects the range from:

Red: C2
Green: Down column C
Orange: as far as the last non-blank cell

Then in A2, an array formula returns the alphabetically sorted list of unique agents:
=IFERROR(INDEX(All_Agents,MATCH(0,COUNTIF(All_Agents,"<"&All_Agents)-SUM(COUNTIF(All_Agents,"="&A$1:A1)),0)),"")

This is quite complicated!! But in essence:

Red: it looks at the dynamic range of all agent names
Orange: this bit sorts them alphabetically
Green: and this bit ensures that no value is repeated.

Unique_Names. Then, this named range selects the sorted names:
=OFFSET(Sheet1!$A$2,,,SUMPRODUCT(--(LEN(Sheet1!$A$2:$A$1000)>0)))

It does the same thing as the earlier one... however, I had to use a different formula as the "blank" rows contain formulae and the same type of formula used for All_Agents would include all the blanks.

Data Validation in J2

dat/Data Validation. List is selected and references Unique_Agents

Results in K2, another array formula: (drag down as far as needed):

=IFERROR(INDEX(D:D,SMALL(IF(All_Agents=$I$2,ROW(All_Agents)),ROWS(K$2:K2))),"")

Red: look down the list of unique agents and find those equal to the selected value.
Orange: return the row numbers
Green: return them in ascending order
Blue: return the corresponding value(s) from column D
Black: otherwise return a blank.



Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Use the formulae in THIS file, as I made one small change over the previous version.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Ang. Who are you talking to?

I can't help unless I see what you have done...