how to Create a selective pull–out sheet

1 Make a spreadsheet

Open Numbers and tap Create Spreadsheet, then Blank. This creates a single sheet with one table. Unlike in Excel, you don’t get infinite cells. Tap the formatting brush, then Table to set the size — here it’s 13 columns.

2 enter some data

We want to summarize the data for each person, so start by entering names in the first column. (If you want your summary sheet to show data for each month, you could list months vertically and people horizontally.)

3 Autofill month headings

Type “January” as the next column heading, then tap Cell at the top right of the on–screen keyboard, tap Autofill Cells, and drag the right handle of the yellow box across to fill in 12 months.

enter dummy data

We don’t know what scores out of 100 everyone will get, so for now double–tap the first cell (B2), tap the “=” icon and enter RANDBETWEEN (0,100). Pinch to zoom out, and autofill this across all cells, then down.

5 Add check boxes

Tap “A” to select the first column, pick Column Actions from the menu, and tap Add Column Before. Drag to select Rows 2 to 22 of the new column (A2:22), tap the formatting paintbrush, and set Format to Tickbox.

6 Add a second sheet

Tap the plus sign at the top left and pick New Sheet. Size the default table to two rows and 14 columns. We now want to fill this with the data series for whichever participant is checked on Sheet 1. Time for a VLOOKUP!

7 Write a VLOOKUP

Double–tap cell B2, tap the “=” icon and type VLOOKUP(true followed by a comma, then go to Sheet 1 and drag–select all the cells. Tap the up arrow at the right of the range and turn on all the “Preserve” switches.

8 Repeat for all columns

Add a comma, then COLUMN(),0) and click the green check. In Sheet 2, autofill this function across the row. Go back to Sheet 1 and check one of the boxes. Sheet 2 will now fill with that row’s data.

9 Add a chart

Double–tap cell C1 in Sheet 2, tap “=”, go to Sheet 1 and tap the same cell (“January”). Autofill this across the row. Drag–select all the columns from C onwards, tap “+” at the top right and pick a chart type to add.