What is the best formula/function to get this result?

I have a spreadsheet grouping employees to particular managers. What I want is to make a chart/database of which employees are under what managers, and then when an employee is selected from a drop down, it automatically generates the correct manager in another cell... what is the function/method best suited to do this in excel?

Also, it's important to mention that the lists of employees/managers are not listed on a chart or table within the spreadsheet cells, they are listed in a drop down.

any idea? I haven't found a best way to accomplish this yet.

Hi,

Easiest way is to use lookup or vlookup

Ed

Originally Posted by EdMac

Hi,

Easiest way is to use lookup or vlookup

Ed

thats what I had been reading though, however the data I'm using it not displayed on the worksheet, the managers are not listed in any column to reference from. How do you create.. lets say 4 managers with ~15 people under each and then reference that without that data ever been shown on the spreadsheet? Basically, I want to return the managers name when I enter the employees name, but have no list on the spreadsheet in view.

easiest way would be:

create a new sheet - call it data

In data make a list ot the employees say in A1:A50

In B1:B50 put the manager's name of each employee

Highlight the 2 lists, click on the name box (just above A1) and enter a name, say, manager- this names the table. You can now hide this sheet (format > sheet > hide)

Now in the cell where you want the manager to appear put

=if(iserror(vlookup(Employee_cell_reference,manager,2,false)),"",vlookup(Employee_cell_reference,manager,2,false)

HTH

Ed

N

what VLOOKUP does is

You give it the emplyee name (Cell reference), it then looks in the table for that name and then finds the managers name in the next cell.

The Iserror at the beginning says "if the emplyees name cell is blank or if you can't find it in the table, return a blank value.

I have attached an example of how it works

Ed

Originally Posted by EdMac

what VLOOKUP does is

You give it the emplyee name (Cell reference), it then looks in the table for that name and then finds the managers name in the next cell.

The Iserror at the beginning says "if the emplyees name cell is blank or if you can't find it in the table, return a blank value.

I have attached an example of how it works

Ed

Thanks very much for your time on that, I see how your spreadsheet works.. but I'm still confused... is excel able to find that column, which is on a seperate sheet entirely, simply by the name and column number? Because that doesn't work on my spreadsheet... no matter what I reference, it never returns anything.

my data is on sheet1, and sheet2 has my tables with managers/employees

hah! I had a typo in my name definition for the tables

awesome! you are the man!

Hi Pyrex,

Glad you got there in the end and thanks for the feedback

Ed