Currently Being ModeratedJan 15, 2013 11:51 PM (in response to Peter Thomson)
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.
Currently Being ModeratedJan 16, 2013 12:05 AM (in response to Peter Thomson)
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.
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.