OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Payroll Spreadsheet help

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
RenMan
General User
General User


Joined: 08 Dec 2009
Posts: 5

PostPosted: Tue Dec 08, 2009 7:08 pm    Post subject: Payroll Spreadsheet help Reply with quote

1st post
I am trying to make a payroll spreadsheet in NeoOffice easier to use
it didn't take long to figure out that I can select all the rows in column A then select DATA / Validity / Lists and type in my employees (who rotate by the way) names giving me a drop down to select from a list. I can't just make a permanent template because sometimes I have just 1 or 2 guys on a weekly payroll ... sometimes I have 30, hence the need for a dropdown list


heres what I need to do :

when I input a name in COLUMN A , I'd like for it to 'autofill' that employees last 4 digits of their social into COLUMN B

in essence IF Column A = Mickey Mouse , then Column B = 1234
if Column A = Scooby Doo, then Column B = 5678


-I am always having to go back and reference SSN #'s, I'd like the spreadsheet to autofill the information

regardless of the row
hopefully, I'll be able to select all the applicable rows just like I did to add the names list
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue Dec 08, 2009 8:48 pm    Post subject: Reply with quote

VLOOKUP in combination with RIGHT can return the last 4 digits.
Say somewhere in the document is a two column table, named "Table" and with first column Employee Name and second column SNN#.
When you use the column A drop down selection list to select Mickey Mouse in A2, the formula in B2 could be...

=IF(A2="";"";VALUE(RIGHT(VLOOKUP(A2;Table;2;0);4)))

The VALUE function converts the result of the RIGHT function from text to number. If you need the result to be text then use...

=IF(A2="";"";RIGHT(VLOOKUP(A2;Table;2;0);4))

I've included a sample doc...
http://www.4shared.com/file/168641408/a4a9f1e5/Auto_SSN.html

Ken Johnson
Back to top
View user's profile Send private message
RenMan
General User
General User


Joined: 08 Dec 2009
Posts: 5

PostPosted: Wed Dec 09, 2009 12:31 pm    Post subject: Reply with quote

thanks
I'm going to try that
even though I'm not entirely clear on when and where to input this
functions? probably

thanks for the help and the attachment
Back to top
View user's profile Send private message
RenMan
General User
General User


Joined: 08 Dec 2009
Posts: 5

PostPosted: Wed Dec 09, 2009 2:01 pm    Post subject: Reply with quote

sorry, that didn't work as I had done it
I selected the cell in column B where I input SSN #'s and copy/pasted your code
I created a sheet2 (and named it table)
put my name in a2, my # in b2
still no luck

what am I doing wrong ...... stupid question:
do I need to input anything in between the
=IF(A2="";"";VALUE(RIGHT(VLOOKUP(A2;Table;2;0);4)))

did I use this function in the right spot?
do I need to select both a and b columns in my payroll sheet ?
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Dec 09, 2009 2:52 pm    Post subject: Reply with quote

"Table" is not a sheet name it is the name of the range of cells holding every employee's name and SSN#.
Where in your document are the cells holding every employee's name and SSN#?.

What =IF(A2="";"";VALUE(RIGHT(VLOOKUP(A2;Table;2;0);4))) does is look at the value that is in A2 then, if A2 is blank then B2 is made blank.
If A2 is not blank then it must be a name of one of the employees so Calc then looks for that name in the left column (column 1) of the range of cells named "Table".
When it finds that name in the left column of "Table" it looks at the corresponding SSN# in the right column (column 2) of "Table".
Now, because the VLOOKUP function is inside the RIGHT function and the other value inside the RIGHT function is a 4, Calc only takes the 4 characters on the right side of the SSN# it found in "Table".
Also, the RIGHT function always returns text and so the string of 4 digits that it returns could not be used in calculations (if it ever has to be). So, placing that string inside the VALUE function converts it to a number (which might not be necessary, depending on your reason for placing the last four digits next to each employee's name).

I think you are using the formula in the right spot.
It looks like you haven't named the range of cells holding all the names and SSN#s for VLOOKUP to be able to look up the SSN# corresponding to the name in column A, the column with the drop down selection list.

You don't really have to name the range of cell hplding the names and SSN#s, you can instead just use the address of that range.
So, if the range is $Sheet2.$A$2:$B$50 then you can get away with...
=IF(A2="";"";VALUE(RIGHT(VLOOKUP(A2;$Sheet2.$A$2:$B$50;2;0);4)))
a name is just a fancy address.

Ken Johnson
Back to top
View user's profile Send private message
RenMan
General User
General User


Joined: 08 Dec 2009
Posts: 5

PostPosted: Wed Dec 09, 2009 2:59 pm    Post subject: Reply with quote

ok, first problem
I named Sheet 2 "Table"
I only saw your sheet2 to begin with
only when I saw the notes on sheet 1 was I able to begin naming the cell range

i'm working on it
this is complex for me as I have never worked with something like this

I appreciate your patience

this may take awhile ..... brb (yeah right)



----- also -----
my last 4 begins with a 0
but the cell always ignores it Crying or Very sad

give me a sec Ken , and continued thanks
Back to top
View user's profile Send private message
RenMan
General User
General User


Joined: 08 Dec 2009
Posts: 5

PostPosted: Wed Dec 09, 2009 3:01 pm    Post subject: Reply with quote

ok,

in sheet 2, I am only using the last 4 of the # anyway, does that omit some of the code ?
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Dec 09, 2009 3:03 pm    Post subject: Reply with quote

Since the leading zero is being dropped that means you need to use the version without the VALUE function...
=IF(A2="";"";RIGHT(VLOOKUP(A2;Table;2;0);4))
so that it stays as text.

Ken Johnson
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Dec 09, 2009 3:06 pm    Post subject: Reply with quote

OK, since the right column with the SSN#s is already only the last four digits then there is no need for the RIGHT function, so try...

=IF(A2="";"";VLOOKUP(A2;Table;2;0);4)
Edit: Oops! I didn't remove all of the RIGHT function...
=IF(A2="";"";VLOOKUP(A2;Table;2;0))
Ken Johnson
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group