+ Reply to Thread
Results 1 to 4 of 4

return data validation without interspersed blank rows

  1. #1
    Registered User
    Join Date
    01-23-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question return data validation without interspersed blank rows



    Hi everyone,

    I have a list that has blank rows mixed in. I'd like to return a list without those blank rows to be used as a dropdown menu for a budgeting tool.

    I tried using the offset formula from the link below but this just cut off my list after the first blank rows showed up.

    http://www.excelforum.com/excel-gene...ank-cells.html

    My current list looks something like this:

    Code
    5000-Salary
    5000-01 - Hourly


    6000-Supplies


    7000-Travel


    8000-Contractors

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: return data validation without interspersed blank rows

    Here's a non-arrayed formula (assuming your list is in column B)
    =IF(ROW()-ROW($B$1:$B$3000)+1>ROWS($C$1:$C$3000)-COUNTBLANK($B$1:$B$3000),"",INDIRECT(ADDRESS( SMALL((IF($B$1:$B$3000<>"",ROW($B$1:$B$3000),ROW()+ ROWS($B$1:$B$3000))),ROW()- ROW($C$1:$C$3000)+1),COLUMN($B$1:$B$3000),4)))

    Here's an arrayed formula
    =IFERROR(INDEX($B$1:$B$3000, SMALL(IF(NOT(ISBLANK($B$1:$B$3000)),ROW($B$1:$B3000)),ROWS($A$1:$A1))),"")
    or
    IFERROR(INDEX($B$1:$B$3000, SMALL(IF(LEN($B$1:$B$3000)>0,ROW($B$1:$B$3000)),ROWS($A$1:$A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: return data validation without interspersed blank rows

    See if this one works for you...
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-23-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: return data validation without interspersed blank rows

    Hey guys, neither one of these worked for me and now i have another pressing project to work on. I'll come back to this soon though with more details of how i tried your fixes and where i'm messing up because obviously it worked for you (especially haripopuri)

    cheers,

    Eli

+ 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] find last row with a value in a column interspersed with blanks
    By Bptacek in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-04-2014, 07:00 PM
  2. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  3. [SOLVED] How to insert a blank row between two rows of list names in data validation
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2013, 10:52 PM
  4. [SOLVED] How to avoid blank rows in DATA VALIDATION list?
    By pejoi in forum Excel General
    Replies: 3
    Last Post: 11-24-2012, 10:53 PM
  5. Loading Column Data with blank Rows into Data Validation Box
    By ExcelMonkey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2005, 02:05 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