code to recognize text in a cell and place corresponding info in adjacent cell

[m1980rae]

I am attempting to make an adjacent cell auto populate with the last name of a police officer when the officers badge number is entered. The number of officers is over 100, and right now the excel spreadsheet is set up with a sheet for each day of the week. What I want to do is find a vba code that I can type in the Badge Number and the Officer name will populate in the next cell over. I know entering all the officers in the code will be a little time consuming, but is not an issue. And If I can't apply it to the entire workbook, but need to put it on each page, that isn't a problem either.

I attached a copy. But it looks similar to the following:

Beat Badge Name
1 346 Gunther
2 376 Lamantia

I can't use a function because the number of officers working varies and rows get added and deleted based on the number working on a daily basis and several coworkers use this form, not just me.

Any help would be greatly appreciated.

[Solus Rankin]

maybe Night%20Roster(1).xlsm

[protonLeah]

First, you will have to create a master sheet with each officer's name and id numbers, etcetera. The daily roster would be a blank template that could be copied for each day's daily roster and would then use a simple VLookup formula to populate the appropriate cells. You could also make use of Data Validation drop down lists for either the badge or the name. But, you need the master personnel roster.

[m1980rae]

Thank you so much!!!! That worked perfect.

[Solus Rankin]

Glad to help. And welcome to the forum.

If you are satisfied with the solution please mark the thread as [SOLVED] using the thread tools at the top.

And with any thread you can thank those who have helped you by clicking the * below their post.