Linked Drop down lists and vlookup?

Hello Everyone,

Here's what i am trying to do.. I work in a call center and for every team leader i need to show their stats on a month by month basis. So.. i want a create a drop down list on a sheet where if they choose "Jan" and "Andy" only Andy's stats would display for the month of January.. His stats are broken up into month in Sheet 2. So in the sample workbook that i have provided, sheet 1 is going to be the view i want them to see. sheet 2 is the stat of 1 team leader (each tl would have their own sheet with stats for jan - dec) and sheet 3 is the link sheet (where i have all the tl names and month for the drop down). I have no idea how to go about doing this.
Can we combine drop down lists and vlookup somehow?

Any help would be appreciated.

If you can setup your Sheet2 more like I did in the attachment, then it may be simpler.

Set it up so Names only appear in column A, Stat Month only appear in column B and the stats start in column C...

Then a couple of helper columns can classify each row.

In Sheet2!N1 (new N1)... formula:

=IF(OR(C1="",C1="CALLS"),"",LOOKUP(REPT("z",255),A$1:A1)&"_"&LOOKUP(REPT("z",255),B$1:B1))

copied down

and in O1:

=IF(N1="","",N1&"_"&COUNTIF(N1:N$3,N1))

Then in Sheet1 use formula A5:

=IFERROR(INDEX(Sheet2!C:C,MATCH('View Sheet'!$B$2&"_"&'View Sheet'!$A$2&"_"&ROWS($A$5:$A5),Sheet2!$O:$O,0)),"")

copied down as far as you will need to get all relevant data for all personnel, and copy across all columns of the table.

The formula first checks if either C1 is blank ("") or if C1 contains the string "CALLS". If either is true, then the cell remains blank.

Next we create a concatenated string that includes the last string found in column A to that point and the last string in column B to that point.

LOOKUP(REPT("z",255)... is a technique used to find the last text string in a column. LOOKUP finds the last value in an array that is smaller than or equal to the lookup value, which in this case is, "z" repeated 255 times. Most certainly none of your cells will contain that string, so Lookup returns the last string in the array since it will be the last string that is smaller than "z" repeated 255 times.

The _ is just used to keep the string separate... just a good habit to do for such cases as for example... if you have 2 cells, 1 with say 212 and the other with 145 then you have another 2 cells with say 2121 and in next cell 45. If you concatenate those 2 sets of numbers, both will result in 212145. But they shouldn't really be (maybe sometimes you might want that, but not usually).. so we add a separator to make sure they are different (i.e. 212_145 and 2121_45 are clearly different).


Hope that helps.