auto populate cells based off of list selection

Question

I have drop down lists and dependent drop down lists, but I would like to find a method to populate other cells based off of these selections. For example, my position drop down is predicted by the selection made in the department cell. I would like to get the manager auto populated when you select the position.

How can I get this done?

This is for an onboarding check list where the position held predicts software and hardware requirements. What I would like is if I select one position, it puts in "X" in multiple cells.

Here's what I am working with. The "position" drop down is dependent (via indirect) on what is selected from the "department" drop down. So if I pick "callcenter" for example, my positions available to me are "CSR", "Lead CSR", and "Recovery specialist". Should I select a different department, the list of available positions changes accordingly. Perfect.

However, what I am looking to do is when I select department, the "Manager" field is auto-populated with the name of the manager for that department. In addition, when I select a position, such as "CSR", the "Required" cells for equipment and software are populated with "X".

The idea behind this is when new people are hired, they can enter the new hire's name, select location from the drop down, select exempt or non, select relative department from drop down (done), and select the position from a drop down (done). Then send IT the "form" to get them configured. I know these are "IF" statements, or I assume so anyway, but I am struggling with the syntax for multiple if statements in a single cell.

Yes, I know this needs formatting work still.

IT Onboarding

Answer 1

You'll want to look up the information, opposed to making a series of decisions (IF).

Problem 1) Show Manager Name: If you don't already have one, create a table on another tab named "Managers" that lists which managers are in which department. In column A type your departments and in column B type your manager names. On the main tab, this formula will lookup your manager (replace "A1" with whatever cell the department name is):

=VLOOKUP(A1,Managers!A:B,2,0)

Problem 2) Show "X" next to hardware/software: You'll need to create another table on a tab called "Needs" for this step. See screenshot: IT Needs Table

In column A you have a distinct list of positions, and in row 1 you have a distinct list of IT Needs. These names must match the names on your main tab exactly for the lookup to work.

On your main tab, you can use an index/match combination formula to lookup each IT need. I'll assume your IT needs are in one column, starting in A7. You'd then paste this formula in B7 and fill down:

=IFERROR(INDEX(Needs!$A$1:$F$4,MATCH($A$2,Needs!A1:$A$44,0),MATCH($A7,Needs!$A$1:$F$1,0)),"")

Since there are IT needs that won't have a value, I had to surround the index formula with an "IFERROR" so it can show blanks instead of "N/A"