+ Reply to Thread
Results 1 to 2 of 2

Excel tables as Data validation list to exclude blank rows

  1. #1
    Forum Contributor
    Join Date
    02-13-2016
    Location
    MUMBAI
    MS-Off Ver
    Microsoft 365
    Posts
    180

    Excel tables as Data validation list to exclude blank rows

    Hiii..

    I have created an excel table with two columns (1st column has 10 items & 2nd one has 6 items).

    Now I am using this table (2nd column) to create a Data validation list for drop down.

    I have used the following formula in the source list : =Indirect("Table1[column2]") to get all the 6 items dropdown.

    Issue is the dropdown has 4 blank rows below as the table 1st column has 10 items in it.

    I want to exlcude those 4 blank rows from the dropdown.

    What is the solution?

    Thank you.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Excel tables as Data validation list to exclude blank rows

    I don't have a source file, so I will make a lot of assumptions. The table headers are on row 1 and column 2 is column B. The rows with data in column B with data are contiguous.

    Instead of using the table reference, overlay the column with a named dynamic range. Here is a wiki on how to do it: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

    Under the assumptions above the following should work: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

    Then use =Range_Name as the source for the dropdown.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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] Hiding blank rows in a drop down data validation list, help please?
    By A440 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2019, 06:11 AM
  2. Data Validation using list - exclude values taken
    By stribor40 in forum Excel General
    Replies: 33
    Last Post: 04-28-2016, 07:04 PM
  3. [SOLVED] IF Sumproduct function in excel to exclude blank rows
    By newbie4 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2015, 12:22 AM
  4. Exclude items from Data Validation List based on another columns data
    By amartin575 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-19-2013, 11:53 AM
  5. Exclude Empty Cells in a Data Validation List
    By skip2mylew in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2013, 07:05 PM
  6. [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
  7. [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

Tags for this Thread

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