populating cells, based on a selection from a menu - with VBA instead of an Array Index ??

[AlienPump]

Guys,

I was wondering if anyone has an alternative to what I am trying to do, that can be accomplished in a way that doesn't take as much processing power and time, as the way I have it right now?

I basically have a menu, where I make a selection and based on that selection I get a list with a bunch of names of people. Then I have a bunch of vlookups to populate other data, pertaining to those names.

The problem is that with the current setup, where I have the following array formula - if(iserror(index(... small( .. row( - it takes about 10 seconds to populate those names for a small list at the moment (that I want to expand soon, which would make things even worse).

So I was wondering if there's another way to get this accomplished? That's not such a strain on resources? Perhaps with VBA?

Thank you very much in advance for any ideas guys!!!


PS: I have attached a quick and very stripped example of what I am trying to do with just a few items, where it still takes 2-3 seconds (in the lower right corner I see (Calculating (4 processors) % counter)) - https://drive.google.com/file/d/0B6S...w?usp=drivesdk

[AlienPump]

Essentially I am looking for the same functionality of a pivot table, but instead of controlling it with the "filter", to control it with a data validation drop down menu, if that makes sense.
And hopefully there's a way to do it without slicers.

[vcoolio]

Hello AlienPump,

Try the following code in a standard module and assigned to a button:-

Please Login or Register  to view this content.

Once a name is selected in sheet1 cell B2, the code uses autofilter to filter all the values associated with that name in sheet2(Roster),Column D and then transfers the relevant name and language to sheet1(Manager).

I've attached your sample workbook with the code implemented and the pivot table removed.
Select a name from the drop down in sheet1, cell B2 and then click on the "RUN" button.

I hope that this helps.

Cheerio,
vcoolio.