Change cell bases on contents of another cell

JonnyOxford

New Member
Joined
Feb 12, 2019
Messages
3
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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)
 
Last edited:
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top