comparing name and dept and then show job title

I am making a simple timecard in which some employees will have a different job title in different departments. If I leave it up the them, it always gets screwed up. I am trying to make it fool proof.

I would like to make a formula that analyzes a persons name, then looks at their department, and results in displaying the job title in that cell (obviously from a table that i would create with thier correct job titles matched to the department). They will pick the name and department (those are the numbers that ar 4805, 3806, and 2813) out from a drop down list so they are exact.

Brian

4805 Eng Tech
3806 Janitor
2813 Labor

Jimmy

4805 EE
3806 Editing
2813 Manager

So basically someone would click on first cell and from drop down list pick "Brian". Then from second cell pick from a drop down list and choose "3806". Then magically in the third cell "janitor" shows up.

I know how to do the drop down lists, but not go to this level.

I would appreciate any help!

- Brian

This was just a quick throw together, maybe you can use some or all.
Is this what you are thinging of?

Let me know what you think.

Attachment 174515

Very close, but missing one aspect. Thank you very much for the help, though. I wish i could whip out these formulas like you guys.

The thing is that different people qualify (per thier resume) in different job numbers. As in:

Brian qualifies as an Eng Tech when chargin to 4805, but qualifies as a janitor on 3806
Jimmy qualfies as a EE when charging to 4805, but qualifies as an editing on 3806

I need them to pick thier name from a drop down, then pick various departments from a drop down list and then the job correct jpb title automatically pops up in the next cell that specficially pertains to what they qualified for. Is that doable?

f13@fathom4

I think I have what you are looking for now, on the main sheet there is a button.
Click on it and Choose the Employee Name, Next Job Number and a Description will show up.
From there Click on the Add Employee Info and it will be added to the Speadsheet, you have the choice of adding additional name if you choose
The macro will find the next available row and add the info to that row.

Give it a spin and let me know what you think.
If this is a winner, I will upload a revised sheet.
I will comment out the code and describe within the Sheet what the areas are, how they work, and what to do if you want to add or delete names.

Sample Names From List.xlsm

I think this should work, find the cells marked to see what formulas i've used. You have to fill the sheet "source" yourself for column A,B and D. In column C a unique key will be generated which combines name and department. Then on sheet "output" you have to put in the name in column A and department in column B and then in column C the correct job description will appear, or a - when the name and department combined do not exist in source sheet. You will have to create your own drop down list for names and departments.
Please do bear in mind that when two names within a department with different jobs are included this will not work.


Name department uplook.xlsx

You did it!!! That is perfect and it works in my sheet perfectly!! Thank you so much for your help. I hope you ahve nothing but good fortune from here on out. If I am ever in Rotterdam I will have to buy you a drink.

- Brian

@ [email protected]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.