Change cell bases on contents of another cell

[JonnyOxford]

Good Morning,

I have a list of approximately 100 names. Every morning they clock in to a finger print scanner. I receive this as a simple excel spreadsheet which shows their name and clock in time.

I need to be able to add a extra cell next to their name with their job title so that I can then run COUNTIF to see how many men from each job title are working. I have an excel spreadsheet with their names and job roles so I just need to be able to link them somehow?

Because the names always come in in a different order and often there are people who don't turn up the sequence changes everyday, otherwise I could have sorted the data and simply copy and pasted the job titles from the day before.

The reason for this is that I need to produce a graph at the end of each week showing how many of each job title have worked during the week.

I hope this makes sense.

Thanks

 

[RasGhul]

Can you post some sample data?

 

[gaz_chops]

Try vlookup,

=vlookup(name to lookup, sheet & columns with name & job title,2,0)

e.g.
=VLOOKUP(A1,Sheet2!$A$1:$B$30,2,0)

 

[JonnyOxford]

Can you post some sample data?

I've uploaded the file to WeTransfer. The first sheet is the data I received today, the second sheet is the information I have made. I have removed their names and replaced with name 1, name 2..... I want to make a new column on the first sheet which will look at sheet 2 and see that Name 1 is a Concrete Finisher so populate the cell with this title.

I have two jobs with very similar data, this is the smaller of the two.




https://we.tl/t-JrN4aQSUF1

 

[gaz_chops]

Did you try vlookup?

 

[RasGhul]

Hi Johnny,

Just like as gaz chops suggested vlookup will populate Column E with roles from sheet 1 for your countif;

=VLOOKUP(C4,'Job Titles'!$D$1:$E$46,2,0)

 

[JonnyOxford]

Awesome that works. Please excuse by excel knowledge.

Thanks for your help gaz_chops and RasGhul!!

 

[gaz_chops]

You're welcome, glad it's working.