0

I need help creating a formula in Excel 2010.

What I want to be able to do is:

When I choose a name from a dropdown-list in a random cell in one column, I want that name to be coupled up with that name's employee number etc. So when one employee opens this shared document, he/her will be able to put in his/her number in a (random) cell adjacent to his/hers name, and the third cell will then automatically show their signature (their name in a different colour etc.)

This code must work in many cells, in many different sheets. There also needs to be one sheet with all the names and numbers in it, that I can hide.

Here's what I have tried:

Sheet 1 with formula Sheet 2 with names and numbers

The names in Sheet 1, (Module 01) is names that is selected using a dropdown list using a range in sheet 2 (Instructors).

When I choose "Tom" from the dropdown-list and enter his code 222 in the adjacent cell his signature comes up with this code. When I enter "Hans" and his code in adjacent cell his signature comes up. But I can't get any further. I want to be able to chose whoever from the dropdown-list, write that persons number and have their signature appear.

If the wrong number is entered, there will be shown a blank cell.

  • 1
    Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including any scripts they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for scripts are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?. – eirikdaude Feb 18 '16 at 8:25
  • Besides trust, what would stop people from filling in fields for other users? This sounds like some kind of time sheeting or authorisation system and while it is workable it seems like doing it in excel leaves it open to abuse. It would be better if you could show us what you have so far and what formulas your initial test sheet has. – Mokubai Feb 18 '16 at 8:39
  • This can be abused, absolutely, but what I have edited now is the testdocument, that I wanted to show you what I am thinking. When I find a "code" that works, I can use that in the original document :) The original document can not be shared online.. – Ole Morten Feb 18 '16 at 8:45
0

Use a VLOOKUP to find the name of the person in the codelist, and then grab the "code" from column B (column two in the range given as the second argument to the function) in the codesheet:

=IF(VLOOKUP($A1;Sheet1!$A$1:$B$4;2;FALSE)=$B1;$A1;"")

Return the name entered in column A if the two equal each other, if not return an empty string.

Sheet 1 (the hidden codesheet) using this solution: enter image description here

Sheet 2 (the one where you enter info) using the formula above: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.