+ Reply to Thread
Results 1 to 6 of 6

Skip blank spaces in data validation list

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    Jupiter
    MS-Off Ver
    Excel 2013
    Posts
    78

    Skip blank spaces in data validation list

    I have a range that has a lot of blank spaces and I want to make a list out of that range, but I want that list to only include the cells with data in them. How do I do this? Attached is an example.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Skip blank spaces in data validation list

    You could derive a second list with the numbers bunched up, and use that as the source of your data validation. For example, put this formula in H1:

    =IFERROR(SMALL(Numbers,ROW()),"")

    and copy down to H9. Then your data validation could be for this source:

    =INDIRECT("h1:h"&COUNT($H$1:$H$9))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-01-2013
    Location
    Columbus, ms
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: Skip blank spaces in data validation list

    Quote Originally Posted by Pete_UK View Post
    =IFERROR(SMALL(Numbers,ROW()),"")
    I'm so glad I got involved with this forum. I never knew about "Small" and "Large" until coming here and never thought of this use of it. Thanks!

    Phil

  4. #4
    Registered User
    Join Date
    01-14-2013
    Location
    Jupiter
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Skip blank spaces in data validation list

    How do I specify the range I want to include in the first formula?

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Skip blank spaces in data validation list

    You already had a named range called Numbers in your example sheet, referring to the range:

    =Sheet2!$A$1:$A$9

    So, you can either specify the range instead of that name, or use a named range like I have.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    01-14-2013
    Location
    Jupiter
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Skip blank spaces in data validation list

    Oh yeah, I forgot haha. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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