Inherited Staff Roster Nightmare

[DeborrahC]

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

NAME

WKR #

PHONE #

POS. #

ITEM #/CLASSIFICATION

FUNCTION

MAIL STOP#

LANGUAGE

N.O.S.

CU/RM

<tbody>
</tbody>

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

Wkr.

Phone Number

Staff Name

Pos. #

Item # / Classification

FUNCTION

Mail Stop#

Bi-linqual

<tbody>
</tbody>

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!

 

[mumps]

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?

 

[SpillerBD]

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.

 

[ddhuggi]

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]