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[TABLE="width: 500"]
<tbody>[TR]
[TD]Date received[/TD]
[TD]RMA#[/TD]
[TD]Customer[/TD]
[TD]Model Number[/TD]
[TD]Serial Number[/TD]
[TD]cal[/TD]
[TD]ship date[/TD]
[TD]ship from[/TD]
[TD]Manufacturing Date[/TD]
[/TR]
[TR]
[TD]9-18-17[/TD]
[TD]12345[/TD]
[TD]****[/TD]
[TD]BT-1000[/TD]
[TD]BT-692[/TD]
[TD]c[/TD]
[TD]1-20-17[/TD]
[TD]UT[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

Serial Numbers
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Customer[/TD]
[TD]Model[/TD]
[TD]Serial Number[/TD]
[/TR]
[TR]
[TD]1-8-97[/TD]
[TD]****[/TD]
[TD]BT-1000[/TD]
[TD]BT-692[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Date Received[/TD]
[TD]RMA#[/TD]
[TD]Customer[/TD]
[TD]Model Number[/TD]
[TD]Serial Number[/TD]
[TD]cal[/TD]
[TD]ship date[/TD]
[TD]ship from[/TD]
[TD]Manufacturing Date[/TD]
[/TR]
[TR]
[TD]9-18-17[/TD]
[TD]12345[/TD]
[TD]****[/TD]
[TD]BT-1000[/TD]
[TD]BT-692[/TD]
[TD]c[/TD]
[TD]1-20-17[/TD]
[TD]UT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

WorkBook1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1-8-97[/TD]
[TD]****[/TD]
[TD]BT-1000[/TD]
[TD]BT-692[/TD]
[/TR]
[TR]
[TD]2-9-99[/TD]
[TD]****[/TD]
[TD]BT-2000[/TD]
[TD]BT-795[/TD]
[/TR]
</tbody>[/TABLE]

*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]