One of the most popular posts on this blog, is one I published back in 2011, titled How to automatically pull data between different google spreadsheets I am often asked by people, is it possible to filter the data based on the value in a column, before pulling the data across?
I have created a video which shows a technique whereby data is filtered internally within a workbook, so data is pulled onto subsequent sheets, based on values in a certain column. In this example I am using a class of students, and all the grade A students are copied onto a sheet called “A”, all the grade B students are copied onto a sheet called “B” etc. This principle could easily be used to organise a list of sales by sales rep, or by region.
The video is about 13 minutes long, but well worth watching, if you are interested in this technique.
For this example I have used Excel, but this would also work with other spreadsheet systems such as OpenOffice or Google Sheets.
The file used in the video example can be downloaded here, if you want to see or copy the formulas used.
The mechanism also uses a technique to display the sheet name in a cell in the spreadsheet. More details on this can be found at https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-spreadsheet/
I hope that this helps people to make better use of Spreadsheets, whether it is in education, work, or for personal use.