Inherited Staff Roster Nightmare

DeborrahC

New Member
Joined
May 15, 2018
Messages
1
I started a new job and inherited a roster of 500 employees. the woman who created it has two sheets in the book - one an alphabetical listing of the staff with the following 10 fields
[TABLE="width: 914"]
<tbody>[TR]
[TD="class: xl63, width: 141"]NAME

[/TD]
[TD="class: xl63, width: 64"]WKR #[/TD]
[TD="class: xl63, width: 82"]PHONE #[/TD]
[TD="class: xl63, width: 64"]POS. #[/TD]
[TD="class: xl64, width: 170"]ITEM #/CLASSIFICATION[/TD]
[TD="class: xl63, width: 97"]FUNCTION[/TD]
[TD="class: xl63, width: 64"]MAIL STOP#[/TD]
[TD="class: xl63, width: 104"]LANGUAGE[/TD]
[TD="class: xl63, width: 64"]N.O.S.[/TD]
[TD="class: xl63, width: 64"]CU/RM[/TD]
[/TR]
</tbody>[/TABLE]

The other, formatted for distribution to staff, has work group/unit titles, and the names are listed by function. The fields are slightly different

[TABLE="width: 661"]
<tbody>[TR]
[TD]Wkr.[/TD]
[TD="colspan: 2"]Phone Number[/TD]
[TD="colspan: 2"] Staff Name[/TD]
[TD][/TD]
[TD]Pos. #[/TD]
[TD="colspan: 2"]Item # / Classification[/TD]
[TD]FUNCTION[/TD]
[TD] Mail Stop#[/TD]
[TD]Bi-linqual[/TD]
[/TR]
</tbody>[/TABLE]


What is killing me is that they aren't linked in any sort of way, and I must MANUALLY type in new staff, phone number changes, etc on both sheets. MANUALLY. This is making me feel crazy. There has to be a way to set this up to pull the data from the alpha list and put it into the format which is distributed to staff without manually inputting the same data twice.

Any ideas? Is Excel even the best tool to use for this or should I be looking into something else? My boss is willing to spring for whatever I need, but I'm not sure what that might be.

Help!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Are you saying that as you enter data in the alphabetical listing of the staff, you want the sheet for distribution to staff to be populated automatically? Then as you modify data in the first sheet you want the second sheet to also be updated? What goes in the "Bilingual" column?
 
Last edited:
Upvote 0
You're right in that should be driving you crazy.
You should only have the one master list containing all the data you need to associate with each employee.
THEN you can use a reporting method. I always start at a Pivot Table for its organizational/filtering capabilities in such a case. Don't need to use any sigma values.

Access can be more robust for this use. Access has better reporting capabilities in a case like this. However, I'm hard pressed to not use Excel because it can be easier to hand over to someone else down the line.
 
Upvote 0
I would recommend using a vlookup format to pull the information from the opposing sheet. This would allow you to pull specific information or all of it. I am currently looking for assistance on a similar issue. I have the vlookup function working through userform, I am looking to adjust the information and place it back on the same line.

here is my code that allows you to look up the information through VBA. I have attached Item numbers to each line. So you may have to adjust your coding to look at a specific column to pull the information you are looking for.

[Dim a as long
Dim k as range
a = me.textbox1.value 'this is where you would input your item #

Set l = sheets("TCL").Range("A:A") TCL is the name of my sheet and A:A is where I have my item #'s

r = Application.WorksheetFunction.Match (a, l, 0) This is searching for an identical match of the entered information in Textbox1. Then it will return the information found to the following Text boxes.

me.Textbox3.value = Sheet("TCL").Cells(r, 1).value
me.Textbox4.value = Sheet("TCL").Cells(r, 2).value
me.Textbox5.value = Sheet("TCL").Cells(r, 3).value
me.Textbox6.value = Sheet("TCL").Cells(r, 4).value

End Sub]
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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