Generating a 2nd list based off a 1st list.

Hello,

I am trying to have 2 lists, first list will be a manager criteria, the 2nd would be a associate list that has selections based off the 1st list. Currently the only list I have added is the associate list but it lists all associates. It is kind of a cheap way to do it too, I copied all associates to another sheet to have them available for the list. I know there is a better way to handle this, but I am failing right now lol. The lists would be under the Associate Graph sheet.

I have attached a copy of the file that has been edited down. Normally there is over 5000 lines of data on the overall data tab. I also removed 8+ sheets that arent relevant to what I want to do here. I also removed some formulas on the Associate Graph to keep it simple for now as I update everything based on other sheets. And the fact it is over 1MB which is the limit here.

The manager for each associate is listed under column F, their ID is listed under column G, and their name is listed in column H currently. This data is listed under the overall data sheet.

I forgot to pull Manager bellow Incentive Group... Just pull it down to the right box and/or organize them as you like...

Originally Posted by zbor

Try with Pivot table (sheet1)

There is much data so now just look at this and then we can continue

I think I may have explained a bit incorrectly. I updated the file and added comments where I am looking to do this. See if that makes it a bit clearer. The lists would need to be on the associate graph sheet as that is where the relevant information will be displayed.

Do you need them to be dependent?

When you choose Manager4 to get only Doe Jane 5 and 9 in other list?

check here: http://www.contextures.com/xlDataVal02.html

Yes I would like them to be dependent. I can use that link to do that, do you know of an easier way for the file to provide me a better data option for the lists? Something like where I have sheet2, but a formula that would look up this information direct from the overall data sheet? Does that make sense? Basically a way to have the info I have on sheet 2 automatically be provided? I will need to add manager name in there somewhere too I would think.

Even if I could somehow pull the list direct from the overall data tab but don't duplicate the names? Currently I am just copying the names and IDs direct from overall data and then removing duplicates. I need to dumb this down as eventually I will not be the one updating this file with the raw data on overall data.

This is easiest way for dependent list.

For creating Unique list you could:

Select column (for example H column in Overal data)
Data -> Advanced filter (next to filter)
Choose: copy to other location and unique only
Define where to copy
Press OK

Or you can make VBA

Once you create Unique records you can use them in validation list.

Does your names change all the time?

Or something like this:

Well, the data can change month to month, to an extent. For example, the manager's can change which is why most of the information is tracked by their employee ID. Their ID can not change, however, we can have new associates every month as we have around 350 at any one time. Some quit, some get hired etc....