• Dave Foord
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,624 other followers

  • Dave Foords Twitter

  • Advertisements

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.


16 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.

  3. Hi Dave, love this video very helpful, however I’m trying to do this with my master sheet being a separate spreadsheet.. is there a way of using importrange together with the vlookup?!

    • Sorry for not replying sooner, but was away over Easter. Yes you can use the importrange in tandem with the vlookup. The easiest approach is to use the importrange to pull the contents of the desired sheet from the master to a separate sheet in the other document. You then use the vlookup as normal. Once set up you can always hide this sheet, so you won’t realise that it is there.

  4. hi Dave, I am receiving a message #N/A when I am trying to use the formula: =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1255)
    I’m using google sheets, can you please advise why this may be occurring? Can you also please let me know where the notes that you refer to in the video (which contains this formula) are located? Thanks

    • I am glad you have found my post useful. The formula for working out the name of the sheet only works in Excel, it doesn’t work in Google docs, and I haven’t found a way of doing it in Google yet unfortunately, so sorry there. In the end I didn’t put the formula into the YouTube notes (as I realised that they would be out of context), instead I shared the file on the blog post which has the formula in, and in context.

      I hope that this helps.

      Dave Foord

  5. Hi Dave, I tried downloading and running the template but it keeps showing file is corrupted, could you advise if there is another link to download?

    • I have just emailed you with copies of the files, can you see if they work and let me know if they still don’t work for you.

      When I download the file it is working fine, and others have downloaded it without any problems.

      • Hi Dave,

        I’m still getting errors off the two files(probably it’s my Excel) but can see it on Google Sheets on Gmail, but thanks anyway. I managed to slowly go through the video and slowly keyed in the commands accordingly and gotten the file I needed up already. 😉

      • Glad you got it sorted in the end. I am not sure why the file didn’t work for you.

  6. Wanted to say thanks for the video however I am running into a problem with the vlookup part of the video. My sheets are not the exact same and the file i downloaded works correctly so I’m not really sure what I’m doing wrong but it has to be me or my formatting. I copied the code from your workbook and just edited the content to change it for me. Whenever I am done with the formula excel opens a save box and ask me to pick a list or something. I hit cancel but nothing seems to move to the other page.

    My hidden a column and b column on the master list are doing exactly what they should and keeping track of my column for changes and changes correctly.

    Here is what is in the formula bar:

    MASTER LIST = The name of the master data sheet, with all the headings and things data lookup starts at cell b8. Not sure what exactly needs to be done with the column() part. I tried adding +1,+2 and didn’t see a difference.

    Hoping i just did something stupid or maybe the syntax is off, wonderful video though, hope someone can help


    • Well remember what I said about it being my own fault after hours of trial and error I found out that it was my SHEET name. MASTER LIST was being seen as page “MASTER” and then it was looking for some type of list or data range. That is why i kept getting the dialog for choosing something to pull values from. I even tried creating my own range and calling it “data” but it would pull values but not update them. The fix to it was changing the MASTER LIST page to MASTER_LIST. Wow what a dummy. Excel and programs in general do NOT like spaces.

      • Well done for sorting this out. I wasn’t aware of that particular behaviour but my suggestion to you was going to be to rename the sheet (I was wondering if the space was the problem), but you got there on your own.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: