QJ.net Game Discussion - PSP, Xbox, Wii, PS3, PSP Homebrew, and PSP Guides

**Realn0whereman** · 07-19-2006, 07:52 AM

I have 2 colums in a spreadsheet that have names and SSN for workers.

i have a third spreadsheet that is formatted a certain way. the third one needs to have the worker right next to his ssn... basically im making a forum that will fill itself in when i type in the workers name.

this is how its basically gonna be structures

NAME SSN field

. the ssn will have a forumula

NAME IF NAME= any name in column A then Fill in SSN on the Row that the name was found on/..... if anyone answers me correctly ill give them points and possibly monies.... PLZZZ HELP

**PopeOfDope** · 07-19-2006, 08:02 AM

OK I'll try and explain.
You need to use the vlookup fuction. Ill give an example
=VLOOKUP('what to look for','absolute cell reference to table','column to return',FALSE)

Replace 'what to look for' with the cell reference for your NAME column on the new table.
Your 'absolute cell reference to table' is a reference to where your first table is i.e. the one with only name and SSN. So for example a table going from A1 to B5 would be $A$1:$B$5
Column to return indicates the number of the column in the first table where the SSN is. In your table, you have the SSN in the 2nd column, so you put the number 2 there.
FALSE just means the forumla requires an exact match rather than a close estimate. Since SSNs can't be estimated, leave it as false.

**Realn0whereman** · 07-19-2006, 08:06 AM

i think the answer is qith macros

**PopeOfDope** · 07-19-2006, 08:08 AM

This will work, I'm pretty sure I have the right formula. Give me a minute to make a quick sample.

**Realn0whereman** · 07-19-2006, 08:18 AM

please and upload it.... i think ur missunderstanding me tho

spreadsheet 1
NAME SSN
Name1 SSN1

spreadsheet 2
NAME(i type this in) SSN (pastes the ssn accordingly)

Double Post Merge

im trying this and i cant go over the other other spreadsheet

**PopeOfDope** · 07-19-2006, 08:19 AM

Goes to

If you are working between different spreadhseets in the same workbook, use the notation.
('spreadseet name'!'cell references')

**Realn0whereman** · 07-19-2006, 08:23 AM

yea but these tables are located on different spread sheets (like diff files)...

**PopeOfDope** · 07-19-2006, 08:25 AM

Reference to another worksheet In the following example, the AVERAGE worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.

=AVERAGE(Marketing!B1:B10 )

Taken from Microsoft Office help.

All you have to do in your case is put 'spreadsheet name'! infront of your absolute cell reference to the original table.

EDIT: just saw your different files comment.
I'm afraid I don't know how to do that sorry.

**Realn0whereman** · 07-19-2006, 08:32 AM

when my boss gets back ill ask if i can merge all the data....****in certified payroll. thanks for trying. im just gonna continue what i was doing till he gets back.

**PopeOfDope** · 07-19-2006, 08:34 AM

Type playing around with the address formula in conjunction with the vlookup formula.
The context of the address fomula is

=ADDRESS(row number,column number,4,"[workbook name]!sheet name")

If you have the names of workers in the same order in both workbooks e.g. alphabetical order, then this should work without any need for the vlookup formula.

btw, the number 4 indicates a relative reference, change it to 1 if you want absolute. You shouldn't need absolute in your situation.

07-19-2006, 07:52 AM	#1
Realn0whereman Mindless Self Indulgence Join Date: Oct 2005 Location: afk Posts: 7,212 Trader Feedback: 0	Excel formula help. reward offered PLZ HELP I have 2 colums in a spreadsheet that have names and SSN for workers. i have a third spreadsheet that is formatted a certain way. the third one needs to have the worker right next to his ssn... basically im making a forum that will fill itself in when i type in the workers name. this is how its basically gonna be structures NAME SSN field . the ssn will have a forumula NAME IF NAME= any name in column A then Fill in SSN on the Row that the name was found on/..... if anyone answers me correctly ill give them points and possibly monies.... PLZZZ HELP __________________ PSN:realn0whereman NEW MSI ALBUM APRIL 29TH: IF orgasm

07-19-2006, 08:06 AM	#3
Realn0whereman Mindless Self Indulgence Join Date: Oct 2005 Location: afk Posts: 7,212 Trader Feedback: 0	i think the answer is qith macros __________________ PSN:realn0whereman NEW MSI ALBUM APRIL 29TH: IF orgasm

07-19-2006, 08:18 AM	#5
Realn0whereman Mindless Self Indulgence Join Date: Oct 2005 Location: afk Posts: 7,212 Trader Feedback: 0	please and upload it.... i think ur missunderstanding me tho spreadsheet 1 NAME SSN Name1 SSN1 spreadsheet 2 NAME(i type this in) SSN (pastes the ssn accordingly) Double Post Merge im trying this and i cant go over the other other spreadsheet __________________ PSN:realn0whereman NEW MSI ALBUM APRIL 29TH: IF orgasm

07-19-2006, 08:23 AM	#7
Realn0whereman Mindless Self Indulgence Join Date: Oct 2005 Location: afk Posts: 7,212 Trader Feedback: 0	yea but these tables are located on different spread sheets (like diff files)... __________________ PSN:realn0whereman NEW MSI ALBUM APRIL 29TH: IF orgasm

07-19-2006, 08:32 AM	#9
Realn0whereman Mindless Self Indulgence Join Date: Oct 2005 Location: afk Posts: 7,212 Trader Feedback: 0	when my boss gets back ill ask if i can merge all the data....***in certified payroll. thanks for trying. im just gonna continue what i was doing till he gets back. __________________ PSN:realn0whereman NEW MSI ALBUM APRIL 29TH: IF orgasm*

07-19-2006, 08:02 AM	#2
PopeOfDope DopaMean Join Date: Jun 2005 Location: London UK Posts: 3,739 Trader Feedback: 0	OK I'll try and explain. You need to use the vlookup fuction. Ill give an example =VLOOKUP('what to look for','absolute cell reference to table','column to return',FALSE) Replace 'what to look for' with the cell reference for your NAME column on the new table. Your 'absolute cell reference to table' is a reference to where your first table is i.e. the one with only name and SSN. So for example a table going from A1 to B5 would be $A$1:$B$5 Column to return indicates the number of the column in the first table where the SSN is. In your table, you have the SSN in the 2nd column, so you put the number 2 there. FALSE just means the forumla requires an exact match rather than a close estimate. Since SSNs can't be estimated, leave it as false.

07-19-2006, 08:08 AM	#4
PopeOfDope DopaMean Join Date: Jun 2005 Location: London UK Posts: 3,739 Trader Feedback: 0	This will work, I'm pretty sure I have the right formula. Give me a minute to make a quick sample.

07-19-2006, 08:19 AM	#6
PopeOfDope DopaMean Join Date: Jun 2005 Location: London UK Posts: 3,739 Trader Feedback: 0	Goes to If you are working between different spreadhseets in the same workbook, use the notation. ('spreadseet name'!'cell references')

07-19-2006, 08:25 AM	#8
PopeOfDope DopaMean Join Date: Jun 2005 Location: London UK Posts: 3,739 Trader Feedback: 0	Reference to another worksheet In the following example, the AVERAGE worksheet function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook. =AVERAGE(Marketing!B1:B10 ) Taken from Microsoft Office help. All you have to do in your case is put 'spreadsheet name'! infront of your absolute cell reference to the original table. EDIT: just saw your different files comment. I'm afraid I don't know how to do that sorry.

07-19-2006, 08:34 AM	#10
PopeOfDope DopaMean Join Date: Jun 2005 Location: London UK Posts: 3,739 Trader Feedback: 0	Type playing around with the address formula in conjunction with the vlookup formula. The context of the address fomula is =ADDRESS(row number,column number,4,"[workbook name]!sheet name") If you have the names of workers in the same order in both workbooks e.g. alphabetical order, then this should work without any need for the vlookup formula. btw, the number 4 indicates a relative reference, change it to 1 if you want absolute. You shouldn't need absolute in your situation.

QJ.net Game Discussion - PSP, Xbox, Wii, PS3, PSP Homebrew, and PSP Guides

Excel formula help. reward offered PLZ HELP