excel macro

[elenamccarthy]

I want to look at one excel sheet called "shipping and receiving log", I want it to find a "serial number" ex. W-111, then go to another excel sheet called "Serial Numbers" and find the correct instrument type ex. Worker. I want it to find the matching serial number from the first excel sheet ex. W-111, locate the date ex. 1-28-10 and then input it into the "shipping and receiving log". How would I write this up? Any code is appreciated.

 

[Nine Zero]

perhaps something like this in the cell where you want your result

=VLOOKUP(A1,'Serial Numbers'!A:B,2,0)

where the following are true

A1 = the cell that contains W-111 in your "shipping and receiving log" sheet

'Serial Numbers'!A:B = a table where the first column "A" is a list of all possibilities of W-111 and the second column "B" are your dates

if you give me some more info i can make it easier

 

[elenamccarthy]

I have a serial numbers workbook(workbook 2) with all of the serial numbers from all our different instruments. I have another workbook labeled shipping and receiving(workbook 1). I want to start in the workbook 2 and have it locate a serial number and then open workbook 2 and find that serial number and grab the date the instrument was made and put it back into the workbook 1

 

[elenamccarthy]

Shipping and receiving Log

Date received	RMA#	Customer	Model Number	Serial Number	cal	ship date	ship from	Manufacturing Date
9-18-17	12345	****	BT-1000	BT-692	c	1-20-17	UT	?

<tbody>
</tbody>

Serial Numbers

Date	Customer	Model	Serial Number
1-8-97	****	BT-1000	BT-692

<tbody>
</tbody>

Grab 1-8-97 and put it in the manufacturing Date.

 

[Nine Zero]

Alright so this is how the data is structured for me

WorkBook2

A	B	C	D	E	F	G	H	I
Date Received	RMA#	Customer	Model Number	Serial Number	cal	ship date	ship from	Manufacturing Date
9-18-17	12345	****	BT-1000	BT-692	c	1-20-17	UT

<tbody>
</tbody>

WorkBook1

A	B	C	D
1-8-97	****	BT-1000	BT-692
2-9-99	****	BT-2000	BT-795

<tbody>
</tbody>

*The last line was just added for testing purposes*

So in cell I2 this is the formula
=INDEX([Book3]Sheet1!$A$2:$C$3,MATCH(E2,[Book3]Sheet1!$D$2:$D$3,0),MATCH("Date",[Book3]Sheet1!$A$1:$D$1,0))

Im sure after you paste it you can make sense of it

Give it a try

 

[elenamccarthy]

What is Book3? I tried it and i got #N/A. To have it find the unit serial number how do i get it to look for it.

=INDEX([Book3]Sheet1!$A$2:$C$3,MATCH(E2,[Book3]Sheet1!$D$2:$D$3,0),MATCH("Date",[Book3]Sheet1!$A$1:$D$1,0))

Could you break it down for me so that i can try and debug it?

 

[Nine Zero]

[Book3]Sheet1! needs to be changed to your workbook 1 name and your sheet 1 name

So lets say your workbook 1 is called SerialNumbers and the sheet is SerialNum

the formula would then be

=INDEX([SerialNumbers]SerialNum!$A$2:$C$3,MATCH(E2,[SerialNumbers]SerialNum!$D$2:$D$3,0),MATCH("Date",[SerialNumbers]SerialNum!$A$1:$D$1,0))

 

[elenamccarthy]

What if i have different 20 different instruments and they have their own sheet name? Does it matter if the name of the workbook has a space in it and the sheets are the 20 different instruments?

 

[elenamccarthy]

After i enter the formula and hit enter i get #N/A. Please help not sure what i am doing wrong. Here is the code i'm using.
=INDEX([SerialNumbers]Sheet2!$A$2:$C$3,MATCH(E3,[SerialNumbers]Sheet2!$D$2:$D$3,0),MATCH("Date",[SerialNumbers]Sheet2!$A$1:$D$1,0))

 

[elenamccarthy]