Excel Help

[lozz]

Hello,

I am creating a new spreadsheet. I have two columns - 1) Department and 2) Manager. I have made a "List" for the department - such as HR, Recruitment, etc.

My plan is for when you select the department from the drop down list, the managers name automatically is populated under the 2nd column "Manager".

What forumla can i use for this to happen? I have created a new tab with the Department names and the Managers names against each one so that i can use this for any forumla i do...

any help and gudiance is appreciated.

Thank you.

 

[Trevor G]

Welcome to the Forum,

VLookup or Index and Match should do what you want.

 

[lozz]

Thank you.

So i have tried VLOOKUP but i feel i may be doing the formula wrong?

 

[Joe4]

Why don't you post your formula here, and explain the details of the data structure (i.e. what columns the values you are looking up are in, and what columns the values you want to return are found in)?

 

[Trevor G]

Look at this

Book1
	A	B
1	Dept Name	Manager
2	Products	Anne
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IFERROR(VLOOKUP(A2,DeptMan,2,FALSE),"")

Named Ranges
Name	Refers To	Cells
DeptMan	=Departments!$A$1:$B$5	B2

On the Departments Sheet

Book1
	A	B
1	Dept Name	Manager
2	HR	Keith
3	Sales	Tony
4	Finance	Sarah
5	Products	Anne
Departments

 

[lozz]

Pic1 - This is my main spreadsheet. I have a drop down list as you can see of the departments. I want to select "HR" for example, and for Joe Bloggs name to appear in column B,

Pic 2 - this is my second tab where the managers names are populated against each department so that I can use that info to transfer across to the main spreadsheet so that it auto-populates column B.

How do i populate the VLOOKUP formula?

Sorry it is probably really simple... I just havent used Excel in a long time. appreciate your help.

 

[Trevor G]

Very basic steps:
The sheet that holds the department names and Managers Names - Highlight the table and to the left of the formula bar is the name box tap it and type in DeptMan and press enter.
Now go to the sheet where you have your drop down and tap the cell below Manager. Copy the formula from my first reply and it should work. Then pull the formula down as far as you need to go.

 

[Joe4]

OK, for row 12, enter this formula in cell B12:
=IF(A12="","",VLOOKUP(A12,Sheet2!$A$2:$B$5,2,0))
and you should be able to copy this formula down for all rows.

Note, if the name of yous sheet that has these Managers on it is not named "Sheet2", just put the real name of the Sheet there instead.
If there are any spaces in the name, you will need single quotes around, i.t.
'Mgr Sheet'!$A$2:$B$5

 

[lozz]

Thank you so much!! It has worked.

Thank you for taking the time to explain this to me... i may be back with more questions relating to other excel bumps i hit LOL!

 

[Trevor G]

Happy to help interestingly you will more than likely get different methods to find a solution.