Forum Discussion

JET_Home's avatar
JET_Home
Copper Contributor
Dec 24, 2019
Solved

How do I have an address selector in Excel please?

Hi Just set up an invoicing spreadsheet which is formatted exactly as needed! Fallen at the last hurdle though.. I cannot work out how to import the customers name and address into the invoice. Each...
excel
Formulas and Functions
  • PReagan's avatar
    PReagan
    Dec 24, 2019

    Hello JET_Home,

     

    It sounds like your situation requires Data Validation for a drop-down list and VLOOKUP() or INDEX()+MATCH() to pull customer address details.

     

    Can you share a sample workbook (without sensitive information)?

mathetes's avatar
mathetes
Gold Contributor
Dec 24, 2019
Without seeing your workbook it's hard to be certain what the best way is. I have created what might be a similar workbook for my wife's consulting work, and I use VLOOKUP to retrieve the specific addresses, as applicable.

There are other ways, but that's probably the simplest. I am assuming that you have your first page set up as a table (or range with all of those data elements you mention arrayed in a single row per customer

If you need more help, please upload a sample of your workbook (after first removing personal information on customers, etc.)
JET_Home's avatar
JET_Home
Copper Contributor
Dec 29, 2019

mathetes Thanks very much for this. I'll attach the blanked spreadsheet - addresses will be listed on Info page and I need to be able to bring into all new invoice sheets which come off my template

 

  • mathetes's avatar
    mathetes
    Gold Contributor
    Dec 30, 2019

    JET_Home 

    In the attached, I have used VLOOKUP to populate the section next to "To:" in your template. That's ALL I've done, however. There's a yellow background cell (H1) where I ask that you enter the customer code, the first column in your customer database. I did remove the first row in that database, where there were strings of *************** asterisks in some of the fields right under the header row.

     

    I have no idea how you're using the rest of it to assemble the actual costs that are being billed to each customer. I did notice that you've got macros written for something....so it may well be that somehow my VLOOKUP solution is trivial compared with some of the other functionality you've already built in to your template.

     

    There are other aspects of this I would question, but for now, if this use of VLOOKUP answers your question, I'll be satisfied. If you need or want more help come back and explain further what you need. There are people here who are far more comfortable with VBA and macros than I.

Resources