Spreadsheets: How to Sort Data Onto Sub Sheets based on values in a given column

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.

Spreadsheet – sort data onto new sheets Shared

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.

Advertisements

5 Responses

  1. […] Edit 29/03/2017 – Please also look at another related post – https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-va… […]

  2. Thank you for your very easy to follow video. I’ve implemented the steps in a workbook and love the results. However, I’ve run into a bit of a problem. From my master sheet I’m sorting names and info into 2 different sub-sheets. On those 2 sub-sheets, I added a column to then sort the names and info into 7 sub-sub sheets using the same procedures outlined in the video. All works well until I add new names to the master sheet and then sort those names into alphabetical order. Names look great on the 2 sub-sheets and 7 sub-sub-sheets however, the column I added to the 2 sub-sheets to facilitate the sorting into the 7 sub-sub-sheets isn’t locked to the data in the row and therefore doesn’t shift with the info when the new names are filtered in. Any suggestions on how to keep the new data on the 2 sub-sheets locked together with the data coming in from the master sheet? If you need a sample of what I’m describing email me and I’ll send you a copy. Thank you for your help.

    • Hi Sheila – glad you liked the video. The crux of the whole issue is that the sorting mechanism only works if the destination sheets are only ever going to be viewed only – as soon as you try and edit the destination sheets, you are going to have problems, and I cannot suggest a simple solution. It would be possible to write macros to do this, but I am not going to try and explain how to do that here.

      So sorry if I have raised your hopes then dashed them.

      • Thank you for your quick reply. Could you send me in the direction of tutorial or webpage that has the info on the macros I would need?

      • Learning to write macros (Visual Basic) or VBA for Excel as often known, is not a quick something to pick up. I personally learnt from a book, but nowadays there are various online tutorials – e.g. http://www.homeandlearn.org/ and http://www.excel-easy.com/vba.html and many others. You would need to learn quite a bit to create what you want though, so this isn’t a quick fix I am afraid.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: