Hi, I created an invoice on Pages and added fields like <Name> <Address> etc. Now from a spreadsheet I want that pages automatically fills in that information instead of me typing it for every customers. Any idea how to do that? Thank you!
MacBook Pro (13-inch Late 2011), OS X El Capitan (10.11)
Hi Levilip,
The VLOOKUP function will drag details from a database table into an invoice table.
Type a client ID into Cell A2 of the Invoice table.
Formula in B2 of the Invoice table
=VLOOKUP($A2,$ID:$Owing,2)
Formula in C2 of the Invoice table
=VLOOKUP($A2,$ID:$Owing,3)
Formula in D2 of the Invoice table
=VLOOKUP($A2,$ID:$Owing,4)
Formula in E2 of the Invoice table
=VLOOKUP($A2,$ID:$Owing,5)
Format the Invoice the way you want it.
Insert a Page Break after the Invoice.
Print only Page 1.
Testing for Client ID 2...
Yep! Joe Brown and details autofill 🙂.
Regards,
Ian.
There was built-in Mail Merge capability in Pages '09 v4.3 with field codes as you depict. Apple omitted this built-in capability in all releases of Pages v5. Instead, they thought that the average user has the time to waste writing AppleScript code to create faux Mail Merge. This link shows how that is done, by importing a CSV list into Apple Contacts Group, and then unleashing pre-written AppleScript to do the Mail merge.
Due to the lack of Mail Merge in Pages v5.5.3, you don't get the opportunity to open your accounts receivable mailing list spreadsheet in Numbers, and then tell Pages to go fetch that data. You can't embed a Numbers spreadsheet for live updates in Pages. You can copy and paste, but if it is a large spreadsheet, then it won't fit in your document. If you have your A/R list as a table in the Pages document, and you do all that fancy VLOOKUP stuff, as soon as you delete the A/R table, all your formulas in your Invoice are hosed along with your Invoice data. A A/R table can be hidden from site, but it is a PITA to do this, and can impact your existing Invoice page layout.
VLOOKUP can only be done from the same document, in case you were hoping to have the A/R list in a table in a separate landscape Pages document.
Yellowboxes VLOOKUP example does not show that you have to preface the name of your data table. If the A/R list was in a table named Data, then the correct reference to populate the first (A2) cell of the Invoice ID would be:
Once you have the formula in the Invoices A2 cell, you can click on the cell and pull the yellow bottom grip downwards to automatically assign the correct formula to the remaining column items.
Autofill Invoice Info on Pages
