HI STEFAN!
No need to shout.
What you've called "Master Database". I've named "Main"; what you've called "Benefits Package", I've referred to as the "smaller tables." These smaller tables represent only the 'working' parts of the Benefits Package pages—the parts that transfer data from one row of Main to cells in one or more tables on the BP sheet.
You have already designed your BP page and placed single or multiple cell tables on it to receive your data. There's no need to re-do that design process. To implement the method of filling the cells described in my earlier post, all that's needed is to enter the formulas presented, using fixed numbers rather than COLUMN()-1 to specify the column offset, into each cell in one copy of the BP sheet.
As written above, the process requires entering the name of each person. By adding a column to Main and filling that column with a 'serial number' using the formula =ROW()-1, you can fill each BP's cells by entering one of these numbers into a cell added for that purpose. Set the cells borders, fill and text colour all to white (or to match the background colour in the place where that cell is located, and it will be invisible in the print out.
Revised formula below assumes you have added the index/serial column as the new column A on the Master Database table (Named "Main" in the formula, that Main is the only table with that name in the document , and that the single cell table on a BP sheet to contain the number for the person whose data is to be used on that sheet in named "No."
Here's the sample, with person 3 selected in No. Each piece of transferred data is in a separate single cell table with the cell borders set to "none". Position on each is arbitrary (and changeable).
With one BP sheet set up, you'll need to duplicate the sheet as many times as you think necessary, then replace the number in No. with either a fixed number, or a formula which calculates the number from the No. table on the previous sheet. (see below)
=OFFSET(Main :: $A$1,MATCH(No.::$A$1,Main :: $A,0)-1,1) returns value from column B of Main (John)
=OFFSET(Main :: $A$1,MATCH(No.::$A$1,Main :: $A,0)-1,2) returns value from column C of Main (Black)
=OFFSET(Main :: $A$1,MATCH(No.::$A$1,Main :: $A,0)-1,3) returns value from column D of Main (benefit 1)
=OFFSET(Main :: $A$1,MATCH(No.::$A$1,Main :: $A,0)-1,4) returns value from column E of Main (benefit 2)
=OFFSET(Main :: $A$1,MATCH(No.::$A$1,Main :: $A,0)-1,5) returns value from column F of Main (benefit 3)
etcetera for as many tables as there are data columns to transfer.
Duplicate the sheet, and on the new sheet, enter a new number in the No. table.
Unfortunately, when duplicating a sheet, Numbers appends "-1" to the end of the sheet name, rather than incrementing the number from Sheet 2 to Sheet 3. Without stopping to rename each new sheet, this soon becomes unwieldy. With stopping and renumbering each sheet, it soon becomes tedious, especially if editing the formula placed in No. to increment the person number for each sheet/page.
If entering the number directly into each table, you'll need to do all 800 sheets. If using a formula, you can do a subset (say 100, or even 50, then replace one number (in the first BP sheet) before printing the batch to PDF.
Formula for the first calculated person number (2, on Sheet 3)
A1: =Sheet 2::No.::A1+1
On subsequent sheets, doubleclick the cell reference lozenge to make it editable and increment the Sheet number by 1.
When printing,
Enter 1 in No. on Sheet 2, then print pages 2 - 51 to print (to PDF) the first fifty BDs
Enter 51 in No. on Sheet 2, then print pages 2 - 51 to print the next 50.
Etc.
It may be possible to set up all 800 new sheets (all with 1 in the No. table), then use an AppleScript to replace the 1s with serial numbers from 1 to 800. Numbers will likely be pretty slow in responding, which may result in timing issues with the script. You may also find that 800 is beyond the limit of sheets in a single document.
Regards,
Barry
Regards,
Barry
