Feb 20 2019 12:32 PM - edited Feb 20 2019 01:15 PM
I am adding new users from a web page, the information populates to a spreadsheet. That works just fine. One of the fields that populates is the position field. There are a number of possible options here as youd imagine. Each position is assigned certain things, emails, cell phones, laptops. So what I would like to have happen is have excel read the contents of the position cell and populate other columns with yes or no.
so if the position is Executive Director G2 would read ED. then H2 will be email, Ix will be cellphone, and so on. I am trying to set up a long function that says if G2 = ED then H2, yes, I2, yes, etc.
The difficulty is there are 12 possible positions each one needs to have different yes and no answers to the email, cellphone, laptop questions.
Anyone have a solution to this? I am not coming up with one.
Feb 20 2019 01:31 PM
SolutionIf you have a reference table (i.e. a table with the 12 positions along the first column and the Yes/No responses in each of the following columns) you can use the vloolup function to do this. I have made a sample sheet that you can use as a reference.
Feb 20 2019 01:44 PM
could these be put on separate sheets? the reference and the actual data about the employee.
The goal is to have the Manager at a location in our enterprise go to a web site enter the name, number, location, and position of the new employee and then use the position abbreviation as the reference value. For us in IT we would want to see all that information, plus what hardware they need and what applications they need to be setup in. So what if sheet 2 had the references and sheet 1 called them?
what would that look like with Vlookup?
Feb 20 2019 02:03 PM
Hi @Frazz4real,
This can be handled using a MATCH & VLOOKUP function. This looks a bit insane, but it's not as complicated as it seems, so please bear with me. I included my file so you can see exactly what I did.
On one sheet, I created a matrix with the positions listed down the left, the items across the top and a yes or no for each position/item combo. (I started on B in this example):
On another sheet, I have a column that shows the employee name in column A and their position in column B. I copied the item row and pasted it across the top. What I want to do is create a formula for each item that's going to look at the role and the item, then search the matrix to see if that role gets that item. This is where I used the MATCH & VLOOKUP functions. It's a bit of a hassle for one row, but then you can copy the formula to all of the cells and it'll calculate correctly.
Starting with the first item for the first person, I used formula: =VLOOKUP((MATCH($B2,'Item Matrix'!$B:$B)),'Item Matrix'!$A:$L,3,FALSE)
I started with MATCH simply to determine which row the position was on:
Next, I created my VLOOKUP formula. You're going to do this once, then paste it to each column updating just 1 value, so it should be decently easy to do:
Once you've entered this formula for the first person/first item, ensure the answer is correct. Then, copy and paste the formula across the first row for the first person. Now you'll need to update the Col Index No for each column. Mine started with a col index value of 3 in column C, so in column D, I'll need to update that to 4. In column E, I'll change it to 5, in F I'll change it to 6 and so on until I've reached the end.
Now, I can just copy that first row of formulas and paste them down the columns for each person and the formulas will auto-update showing the correct info for the correct person and position.
I hope this helped you accomplish what you want to do!
Feb 20 2019 02:17 PM
Thank you, This is exactly what I need.
Feb 20 2019 01:31 PM
SolutionIf you have a reference table (i.e. a table with the 12 positions along the first column and the Yes/No responses in each of the following columns) you can use the vloolup function to do this. I have made a sample sheet that you can use as a reference.