Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating Department Payroll
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating Department Payroll
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 "Howard R" <Howard wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating Department Payroll
Thank you so much.
"T. Valko" wrote: 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 "Howard R" <Howard wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating Department Payroll
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. -- William<"M" Using 2007 "T. Valko" wrote: 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 "Howard R" <Howard wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automating Department Payroll
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,Shee t2!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,Shee t2!A$1:B$350,2,0),IF(COUNTIF(Sheet2!A$1:A$350,A1)= 0,"No Match","Multiple Matches")) "T. Valko" wrote: 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 "Howard R" <Howard wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding payroll stubs payroll calculator | New Users to Excel | |||
Automating Tab names | Excel Worksheet Functions | |||
Help Please - Automating Row height | Excel Discussion (Misc queries) | |||
Automating using VBA | Excel Worksheet Functions | |||
Calculating Working Days - Department of Pathology | Excel Discussion (Misc queries) |