Automating Department Payroll

[Guest]

Hello, I work for a large company and do my department payroll in Excel and
then hand it in to the general payroll department. In one given column "A" I
have the employees' First initial and last name and in another given column
"B" I have the employees' social security numbers. Is it possible to put my
list of about 350 names and social numbers on the payroll page, hide it, and
then format the cells or put in a formula or macro in column B so that the
correct social security number pops up to correspond to whichever name I have
in column A. Thank you in advance for any feedback.

 

[T. Valko]

Assume Sheet2 A1:B350 is your list of names and the corresponding SSN's.

Sheet1 A1 = R. Howard

Enter this formula in Sheet1 B1 to get the SSN:

=IF(COUNTIF(Sheet2!A$1:A$350,A1),VLOOKUP(A1,Sheet2!A$1:B$350,2,0),"")

Biff

 

[Guest]

Thank you so much.

Assume Sheet2 A1:B350 is your list of names and the corresponding SSN's.

Sheet1 A1 = R. Howard

Enter this formula in Sheet1 B1 to get the SSN:

=IF(COUNTIF(Sheet2!A$1:A$350,A1),VLOOKUP(A1,Sheet2!A$1:B$350,2,0),"")

Biff

 

[Guest]

I like your thought of hideing it! its simple! and nearly mistake free. Copy
sheet 1 (Payroll) to sheet 2 (SSN's 1)and you can even copy to a safty sheet
3 (SSN's 2) as back up
Remove SSN's from Sheet one named Payroll and you can use it when you dont
need to see the SSN's.
When you need to see SSN'S, Unhide Sheet titled SSN's 1 You would do this by
right click on the tab and then click hide or unhide...
Hide remainder of sheets so that only (one) the one tab is showing
(Payroll). You can even link cells as needed to make updateing easy.
I then would begin on creating this payroll in Access Data Base where you
can use forms to update information and hide information, and querry
information. You can use the Excel spreadsheet to create the Access Data
Base, including forms as needed.

 

[Guest]

T. Valko, as usual, has come up with a good solution. The only potential
problem I see is that of two or more employees with the same name. VLOOKUP()
will always stop with the first match. So you might want to modify the
formula he provided somewhat to watch for that:

=IF(COUNTIF(Sheet2!A$1:A$350,A1)=1,VLOOKUP(A1,Sheet2!A$1:B$350,2,0),"Name
Error")

this would return the SSAN when there is 1 match found in the hidden sheet,
or "Name Error" if there are either no names matching or more than one name
matching. By including a second IF within the formula you could even be more
specific about the problem:
=IF(COUNTIF(Sheet2!A$1:A$350,A1)=1,VLOOKUP(A1,Sheet2!A$1:B$350,2,0),IF(COUNTIF(Sheet2!A$1:A$350,A1)=0,"No Match","Multiple Matches"))