Drop-down list help

[Sykovampyr]

Hi Guys,

Stalking the the board today! I have a list of Employee Names in A1:20 and Various sets of figures relating to the the employees in col B,C,D,E,F. I want to put a drop down list next to the area managers names in A22 so when they have a manger in their area I can select this from the drop list and it pulls the data from Col F.

For Example, Joe Blow is listed in A1. In B1 are his hours, C1 Are his Expenses And F1 are his total costs for the month.
In A2 Harry Happy is listed with the same criteria.......

In A22 Sally Saturday is listed, She is Joe's Area Manager that month. I want a list that I can select Joe from a drop down list and it pulls the Data from F1.


Any Advice?

Thanks

 

[jbeaucaire]

For the drop down, use the data validation settings of:

Allow: List
Source: $A$1:$A$20


And that will display the employees you listed above. Let's say you put that in cell B22. In C22, you could then put this:

=IF(B22="", "", INDEX($F$1:$F$20, MATCH(B22, $A$1:$A$20, 0))

...the matching column F info would appear when you select an employee in B22.

 

[Sykovampyr]

For the drop down, use the data validation settings of:

Allow: List
Source: $A$1:$A$20


And that will display the employees you listed above. Let's say you put that in cell B22. In C22, you could then put this:

=IF(B22="", "", INDEX($F$1:$F$20, MATCH(B22, $A$1:$A$20, 0))

...the matching column F info would appear when you select an employee in B22.

Awesome! Works Perfectly! Just one other thing, If I wanted to group three mangers together, would there be a way to do this?

Also, Is there a way to have the text, Please Select in the drop down box?

 

[jbeaucaire]

I don't know what you mean about grouping managers together.

Turn off data validation and put "Please Select" in the cell first, then add the Data Validation.

 

[Sykovampyr]

If I wanted to add three people from A1:20 to an Area manager (Who is Listed in A22) and group their total costs.....

 

[jbeaucaire]

Maybe a SUMIF() then?

=SUMIF(RangeOfEmployeesManagers, Manager, RangeOfEmployeeValuesToSum)

=SUMIF(Sheet2!$A:$A, Sheet1!$A22, Sheet2!$F:$F)