+ Reply to Thread
Results 1 to 6 of 6

Not able to sort sheet and ignore cells with a formula that returns a blank result

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Norwich
    MS-Off Ver
    Microsoft 365
    Posts
    12

    Not able to sort sheet and ignore cells with a formula that returns a blank result

    The attached sheet has a Master List which will contain all the data and do not want anyone to touch in any way. There will not be a vast amount of rows (100 max).

    I want people to use the Timetable sheet (possibly in another workbook). I have used a formula to copy data from rows 2-100. If the original cell is blank I have added an IF formula so if it is blank it will return a blank cell rather than an error formula. If I want to sort data in the Timetable sheet it is ok if I sort Colum A (Date). It sorts all the cells containing data first and all the blanks are after. If however I wanted to sort column F (Surname) A to Z order it puts all the blank cells first and puts those with actual names down the bottom of the 100 row range. Excel obviously applies an alphabetical value to "" which comes before A. Or at least I assume that as Excel sorts numerical values before alpha values and that is why there is no problem when sorting Column A.

    I have tried sorting and not showing blanks. It then sorts ok. However, there may be new names added to the master list and if I do this then they are not showing on the Timetable sheet unless someone unhides data in that column and then hides it again. Is there a better way to copy data from Master list so it is all included (even if more is added later) and then be able to sort it without the problem described above?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Not able to sort sheet and ignore cells with a formula that returns a blank result

    Which is the OLDEST version of Excel that any solution needs to work with? Please update your profile with JUST that version.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    Norwich
    MS-Off Ver
    Microsoft 365
    Posts
    12

    Re: Not able to sort sheet and ignore cells with a formula that returns a blank result

    Thank you. Have updated that to Microsoft 365.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Not able to sort sheet and ignore cells with a formula that returns a blank result


  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    Norwich
    MS-Off Ver
    Microsoft 365
    Posts
    12

    Re: Not able to sort sheet and ignore cells with a formula that returns a blank result

    Thank you AliGW. I have learnt some new things with the Filter function. Haven't been able to make it do it how I was thinking. Filter function only seems to copy info across in the same order it was
    in the original sheet, so cannot select what columns to migrate across or change the order. If a cell in another column is blank it puts a "0" in it. Suppose it makes it obvious it is blank. Still have
    other Filter examples to work through so hopefully one of them will do what I am hoping for. Thanks again.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Not able to sort sheet and ignore cells with a formula that returns a blank result

    Have a look at CHOOSECOLS, SORT and SORTBY.

    When you've done that, share your progress and one of us will finesse it for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Some sort of lookup formula that looks at multiple rows and returns a result?
    By rrafluap in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2020, 07:56 AM
  2. [SOLVED] ignore blank cells that are thew result of a formula
    By moneypennie21 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-22-2019, 08:50 AM
  3. Replies: 5
    Last Post: 05-11-2018, 08:54 PM
  4. Replies: 6
    Last Post: 01-04-2017, 11:20 AM
  5. [SOLVED] Ignore blank cells with formulas or ignore NA() in a formula
    By guiismiti in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-24-2016, 02:13 PM
  6. Sort all cells in range with result which are not blank
    By spangpang in forum Excel General
    Replies: 2
    Last Post: 04-09-2013, 06:17 AM
  7. Formula which returns result in two seperate cells
    By ogreville in forum Excel General
    Replies: 1
    Last Post: 09-27-2011, 03:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1