Hello,

I have two spreadsheets that have some matching values. Fo
example: RE518602. One big spreadsheet has all of the availabl
numbers and there are many small sheets that have some numbers in them
I need to write a macro that will look at all numbers in a small shee
and find the matching number in the main (big) sheet. After I matc
them I need to extract a different value that is in the same row bu
different column (on the big sheet) and paste it in a column in th
small sheet. (All sheets are in separate workbooks.)

If someone can help me with this problem it would be greatl
appreciated.

Thank you
Morr

--
Message posted from http://www.ExcelForum.com

You don't need a macro.

Assume the matching column is in column A on both sheets

in B2 (for example) of the smallsheet, put in the formula

=vlookup(A2,'[Bigsheet.xls]Sheet1!$A$1:$D$500,3,False)

this retrieves the value form column C in the Bigsheet for a match.

It returns #N/A if not found.

You can then drag fill it down the column.

--
Regards,
Tom Ogilvy

"morry " wrote in message
...
Hello,

I have two spreadsheets that have some matching values. For
example: RE518602. One big spreadsheet has all of the available
numbers and there are many small sheets that have some numbers in them.
I need to write a macro that will look at all numbers in a small sheet
and find the matching number in the main (big) sheet. After I match
them I need to extract a different value that is in the same row but
different column (on the big sheet) and paste it in a column in the
small sheet. (All sheets are in separate workbooks.)

If someone can help me with this problem it would be greatly
appreciated.

Thank you
Morry


---
Message posted from http://www.ExcelForum.com/

Ok, I tried to use this but it doesn't like my file name.
Here is what you gave me:

=vlookup(A2,'[Bigsheet.xls]Sheet1!$A$1:$D$500,3,False)

My file name is: All parts(test).xls
The sheet name is: All parts
My locations a
The value in: Column B of the sheet that i am adding the vlooku
to
needs to match with the value in: All parts(test).xls: column A
then i need the value from: column C in All parts(test).xls (which i
on the same row as the match) to paste in column C of the sheet that
am putting this vlookup in.

Here are some ways I tried:
=vlookup(B2,'[All parts(test).xls]All parts!$A$1:$C$5000,3,False)
=vlookup(B2,[All parts(test).xls]Sheet1!$A$1:$C$5000,3,False)
=vlookup(B2,All parts(test).xlsAll parts!$A$1:$C$5000,3,False)

What am I doing wrong?

Thank you
Morr

--
Message posted from http://www.ExcelForum.com

=vlookup(B2,'[All parts(test).xls]All parts'!$A$1:$C$5000,3,False)

I left out the right single quote after the sheet name.

This assumes the all parts(test).xls workbook is open.

An easy way to build such a reference is to go to the formula bar,

=Vlookup(B2,
now go to window, select your workbook name, click on sheet All parts, then
highlight your range and enter a comma, type 3,False) and Enter

--
Regards,
Tom Ogilvy


morry wrote in message
...
Ok, I tried to use this but it doesn't like my file name.
Here is what you gave me:

=vlookup(A2,'[Bigsheet.xls]Sheet1!$A$1:$D$500,3,False)

My file name is: All parts(test).xls
The sheet name is: All parts
My locations a
The value in: Column B of the sheet that i am adding the vlookup
to
needs to match with the value in: All parts(test).xls: column A
then i need the value from: column C in All parts(test).xls (which is
on the same row as the match) to paste in column C of the sheet that i
am putting this vlookup in.

Here are some ways I tried:
=vlookup(B2,'[All parts(test).xls]All parts!$A$1:$C$5000,3,False)
=vlookup(B2,[All parts(test).xls]Sheet1!$A$1:$C$5000,3,False)
=vlookup(B2,All parts(test).xlsAll parts!$A$1:$C$5000,3,False)

What am I doing wrong?

Thank you
Morry


---
Message posted from http://www.ExcelForum.com/

The code you gave me worked thank yo

--
Message posted from http://www.ExcelForum.com