DH Templates

How do I add an address from a code in Excel?

I have created an Excel invoice template for a friends small business. It has 3 cells underneath each other where I need to put the customers address. Instead of typing the whole address everytime I do an invoice for them is there a way that I can just type a code (eg. for Pete's Paving I could use PETPAV etc) and the address would come up? Any help with this would be greatly appreciated. Thanks Renee Thanks scottyboy2188. I have done as you suggested but when i type the code in the template invoice worksheet is comes up with #NAME?. Also the address is in 3 different cells (eg cells A1, A2, A3) how do i get it to 'refer to' all 3 cells?

Public Comments

1. Yes! It's called Names. Not sure which excel you're using...but in Excel 2007 there's "Define Names" option. I found it under the formulas tab. I know it's in excel 2003 if you're using that, just can't remember where. First off, I'd suggest making a worksheet with just addresses in it. For example PETPAV (column A) Pete's Paving, 123 Fake Street (column B) Ok. Now, go into your name definition option and for name, use whatever codes you're using in column A. So for our example, you'll wanna use PETPAV. Leave the scope as workbook. Leave comments empty. Then for "refers to", you wanna set it to B1. Then you're done! Keep in mind that whatever you're typing in column A is not used at all, it's more so you know what the codes are for each address. On your invoice template worksheet, just type =PETPAV and it spits out: Pete's Paving, 123 Fake Street
2. If the previous answer didn't work for you then you might want to try a Vlookup approach. To do this you need to set up a data base range (in your case 6 cols.). The first column would be for your code name, 2nd col for your 1st cell , 3rd col for your 2nd cell and 4th col for your 3rd cell. (I wish I knew what you want in the 3 cells. I almost think you want - Name, Addr, City, State, and in a 5th cell: Zip). I will assume you want 5 cells. For an example for the data base I will use F1 to J7 range for four companies (row 1 will just be titles). Your 5 cells will be A1, A2, A3,A4,A5 and A6 will be the code cell. The 6th will be the code name. In F1 to F5 type your code text. In G1 to G5 type full company name matching code text. In H1 to H5 type addr.. In I1 to I5 type city. In J1 to J5 type State. In K1 to K5 type the Zip. Across row 1 starting at F1 type the titles. - Code, Co. Name, Addr., City, State, Zip. It would be best to name this range (except for titles) Name_addr. To do this , Highlight f2 down to K5 then click in the white box just above row numbers (will show F2) and type Name_addr. This will give a name to your data base range. You Now have to sort this range (F2 thru K5)"ascending". In A6 you will be typing the code name. in A1 type =vlookup(A6,Name_addr,,2,FALSE) In A2 type =Vlookup(A6,Name_addr,3,FALSE) In A3 type =Vlookup( (A6,name_addr,4,FALSE) In a4 type =Vlookup(A6,Name_addr,5,FALSE) In A5 type =Vlookup( (A6,Name_addr,6,FALSE) You will have to use cell locations that fit your needs but I think the Vlookup will work for you. Look in Excel Help for info on Vlookup.