3 Replies Latest reply: Jan 16, 2013 4:39 AM by Peter Thomson
Peter Thomson Level 2 Level 2 (350 points)

I have a sheet that is a customer database, and includes a unique number for each customer, along with their name, address and phone number. In seperate sheets (in the same file), I would like to enter the customer's unique number and have their name, address and phone number populate the cells on invoices and receipts. I am new to this type of formula, so would like to ask if there is anyone who may be able to help me. Thank you in advance, and apologies if this has been asked elsewhere.

  • 1. Re: Auto-entry of customer info on invoice
    Peter Thomson Level 2 Level 2 (350 points)

    Found the answer. For entering a name...

    =IF([Cell for customer number]="","",LOOKUP([Cell for customer number],[Column in customer sheet listing customer numbers],[Column in customer sheet listing customer names]))

    The info [in the brackets] are the cells, which will be entered by selecting.

    Repeated the formula for address and phone number.

  • 2. Re: Auto-entry of customer info on invoice
    Barry Level 7 Level 7 (29,180 points)

    Hi Peter,

     

    This is essentially a LOOKUP function.

     

    The table containing the customer number, name, address and phone number is used as a lookup table for a formula like the one below:

     

    Table name: Lookup

    Column A: Customer number

    Column B: Name

    Column C: Address 1

    Column D: Address 2

    Column E: City  ST  Code

    Column F: Phone

     

    On the invoice or receipt table, Customer number entered in cell A1

     

    Formula to return Customer Name: =VLOOKUP(A1,Lookup::$A:$F,2,FALSE)

    Formula to return Phone Number:   =VLOOKUP(A1,Lookup::$A:$F,6,FALSE)

     

    Details on VLOOKUP (and all other functions supported in Numbers) may be found in the iWork Formulas and Functions User Guide. The guide may be downloaded through the Help menu in Numbers '09.

     

    Regards,

    Barry

     

    PS: On posting, saw your own answer to this,

     

    LOOKOUT looks for a "close match" to the search term. If the customer number entered is not in the database, LOOKUP will return information tied to the "largest value less than or equal to" the search term.

     

    VLOOKOUT can do this, but it can also demand an exact match of the search term. If the customer number is not in the database, VLOOKUP (using the FALSE argument shown above) will return a 'not found' error.

     

    B

  • 3. Re: Auto-entry of customer info on invoice
    Peter Thomson Level 2 Level 2 (350 points)

    Thanks Barry, this is really helpful! And I will download the guide.