Find and copy address

[sjfir]

I have a spreadsheet which has a list of customers and addresses (approx 650) and on the next sheet is a list of boxes that we use to complete certain orders, what I am trying to do is input the customer number on the 2nd sheet and this automatically finds the address and inputs this into the box as below.


<TABLE style="WIDTH: 387pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=516 x:str><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=17 width=100 x:num>100101</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=101>Customer Name</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 158pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=211>Address</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=104>Postcode</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=17 x:num>101102</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Customer Name</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Address</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Postcode</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=17 x:num>235110</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Customer Name</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Address</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Postcode</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=17 x:num>351200</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Customer Name</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Address</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">Postcode</TD></TR></TBODY></TABLE>

I then have the following box.


<TABLE style="WIDTH: 300pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=400 x:str><COLGROUP><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" span=2 width=200><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 150pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #e0dfe3" class=xl30 height=21 width=200>ADVICE NOTE</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 150pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl31 width=200> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl26 height=21> </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1pt solid" class=xl27> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt dotted; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext 0.5pt dotted; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl24 height=22>Customer Number</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt dotted; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt dotted; BORDER-RIGHT: windowtext 1pt solid" class=xl25 align=right x:num>235110</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #e0dfe3 0.5pt; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl32 height=21>Address</TD><TD style="BORDER-BOTTOM: #e0dfe3 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl33> </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl34 height=23>Postcode</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1pt solid" class=xl35> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt dotted; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl28 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt dotted; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1pt solid" class=xl29> </TD></TR></TBODY></TABLE>

What I am trying to do is to be able to type a customer number in the box and the address and postcode for that customer autofill into the box.

Any help would be appreciated.

 

[Ruddles]

Which cell of which sheet is the customer number typed into?

Which columns of which sheet are your customer names/addresses/postcodes stored?

If you tell us that, we can write you a VLOOKUP formula with the correct cell references.

Incidentally, you can make your customer number entry box into a drop-down list which displays a list of all your customer numbers... but we can do that later.

 

[sjfir]

Thanks for the quick reply, the customer details are on sheet named Customer Details and the range is from A2 to D650, the customer number we are trying to find is input into B2 of sheet named Advice Note with the aim of populating B3, B4 and B5, with Name, Address and Postcode.

I hope this helps.

 

[gingersteve]

In B3: = VLOOKUP($B$2,'Customer Details'!$A$2:$D$650,2,0)
In B4: = VLOOKUP($B$2,'Customer Details'!$A$2:$D$650,3,0)
In B5: = VLOOKUP($B$2,'Customer Details'!$A$2:$D$650,4,0)

 

[Ruddles]

What GingerSteve said.

The only comment I would make is that by using $A$2:$D$650 as the table reference you'd have to change the formula if the customer table increased in size.

I suspect the lookup will only be marginally - but probably unnoticeably - slower by using $A:$D instead.