Summary sheet in Numbers
Extract data from a table with some handy multi–purpose tricks
Summary SheetS are
commonly used to pull together information. In this example, we’re pulling out a subset of data from a table rather than compiling a new table from multiple sources, using a table of scores for a set of participants in a monthly test. 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. Instead, we can make a single summary sheet that will update with the data for any participant we select.
We’ve used a separate sheet for two reasons: firstly, it’s something you might want to print or export by itself; secondly, we can preserve a distinction between Sheet 1, which is only for entering data, and Sheet 2, which only outputs data. You could use the same techniques with a second table on Sheet 1, if you preferred, and you can lock any table (Paintbrush > Arrange > Lock) to prevent editing.
The key function is VLOOKUP, which returns data from a row selected by searching the leftmost column of a range. We’ll also use RANDBETWEEN to enter some dummy data, which is helpful if you’re creating a template for entering data you don’t have yet. Just select the cells and tap Delete to clear the table for real data when you have it.
You could also add more data to the chart in Sheet 2, such as a line showing someone’s average score so far or the average across all participants in each test.