Reference a column in another workbook

[jpulse]

I am trying to reference a column in another workbook. I have two separate workbooks, MasterList and InStock, the MasterList never changes, but the InStock workbook can change depending on what I have in stock at the time. I want workbook (InStock) to fill in the serial number of its matching part number if it is found in the MasterList workbook. Is there a formula that can do this? Or a Macro that can do this? Thank you for your help
Workbook 1 (MasterList.xlsx)

	A	B	C
1	Part Number	Serial Number	Description
2	50376	AE456	Desktop
3	50421	AE342	Desktop
4	50779	12GX5	Laptop
5	50799	13GD9	Laptop
6	50814	G145FDX	Tablet
7	50836	G129WQE	Tablet
8	63147	A0983456	Cellphone
9	64318	A0123879	Cellphone
10	64347	98UYT654	Scanner

<tbody>
</tbody>

Workbook 2 (InStock.xlsx)

	A	B	C
1	Part Number	Serial Number	Description
2	50376		Desktop
4	50779		Laptop
7	50836		Tablet
9	64318		Cellphone
10	64347		Scanner

<tbody>
</tbody>

 

[MrKowz]

Will the other workbook be open when you want the formula to calculate?

If so, I just need the name of the workbook and the name of the sheet.

If not, I need the full filepath of the workbook (and the name of the sheet). I.e. C:\Users\UserID\Desktop\workbookname.xlsx, sheet name "MasterList"

 

[jpulse]

InStock.xlsx will be the only one open. MasterList.xlsx will be closed, both are in the same folder C:\TEMP\Excel\

MasterList is originally a .csv file, does this create a problem or should it be changed to a .xlsx file?

 

[MrKowz]

What is the sheet name in MasterList.csv that the formula will need to reference?

 

[jpulse]

MasterList

 

[MrKowz]

Try:

=IFERROR(INDEX('C:\TEMP\Excel\[MasterList.csv]MasterList'!$B:$B,MATCH(A2,'C:\TEMP\Excel\[MasterList.csv]MasterList'!$A:$A,0)),"")

 

[jpulse]

Perfect, once again! I really appreciate you help

 

[MrKowz]

Perfect, once again! I really appreciate you help

Cheers!

 

[jpulse]

Another question, I want to create a macro that will put the formula in B2:B6, but the formula gives me an error ever time I run the macro.

Sub Formula()
'
' Formula Macro
'
Range("B2:B6").Formula = "=IFERROR(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)),"")"

End Sub