Results 1 to 6 of 6

Data Validation List excluding blanks

This is a discussion on Data Validation List excluding blanks within the Excel Questions forums, part of the Question Forums category; Hi guys I have a problem with the empty cells in the data validation list I want the list to ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    535

    Default Data Validation List excluding blanks

    Hi guys
    I have a problem with the empty cells in the data validation list
    I want the list to be without the blanks

    I've posted the thread at this link
    http://www.vbaexpress.com/forum/showthread.php?t=33475

  2. #2
    Board Regular
    Join Date
    Jun 2010
    Posts
    535

    Default Re: Data Validation List excluding blanks

    UP

  3. #3
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: Data Validation List excluding blanks

    One way:

    Example data (range A1:A11):

    Test
    Data1
    Data2
    Data4
    Data5
    Data8
    Data10


    Count (range C1:C2):

    Count of Non-blanks
    6


    Formula in C2 is:

    Code:
    =COUNTIF(A2:A11,"*?")
    New table (range D1:D11):

    List Excluding Blanks
    Data1
    Data2
    Data4
    Data5
    Data8
    Data10


    Formula in D2 is:
    Code:
     
    =IF(ROWS(D$2:D2)<=C$2,INDEX(A$2:A$11,SMALL(IF(A$2:A$11<>"",ROW(A$2:A$11)-ROW(A$2)+1),ROWS(D$2:D2))),"")
    Which needs committing with Ctrl+Shift+Enter and can then be copied down.

    Matty

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: Data Validation List excluding blanks

    So that my Data Validation doesn't have empty spaces at the end, I would create a dynamic Named Range as follows (notice how it uses the Count formula already in situ):

    Code:
    =OFFSET(Sheet1!$D$2,0,0,Sheet1!$C$2,1)
    And I'd point the Data Validation to said Named Range.

    All make sense?

    Matty

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    535

    Default Re: Data Validation List excluding blanks

    thank you very much Mr. Matty
    It helped much although it is difficult for me to understand the array formulas
    But it works fine
    Thanks again

  6. #6
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: Data Validation List excluding blanks

    thank you very much Mr. Matty
    You're welcome.

    It helped much although it is difficult for me to understand the array formulas
    I find the Formula Evaluator a very useful tool for breaking down and seeing how formulas work. Also, by highlighting certain sections of formulas in the formula bar and hitting F9, you can see what each part is evaluating to.

    Matty

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com