Create a selective pull-out sheet

“How can we show each participant’s personal progress? Creating a separate table or sheet for every participant would mean a lot of manual duplication. ”

1 CREATE 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 summarise the data for each person, so start by entering names in the first column. (If you wanted 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 (or bottom right of the screen), tap Autofill Cells, and drag the right handle of the yellow box across to fill in 12 months.

4 ENTER DUMMY DATA

We don’t know what scores out of 100 everyone will get, so just for now, doubletap 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 TICK 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 2 rows and 14 columns. We now want to fill this with the data series for whichever participant is ticked 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 tick. In Sheet 2, autofill this function across the row. You’ll see red triangles. Go back to Sheet 1 and tick 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.