+ Reply to Thread
Results 1 to 2 of 2

Getting Drop Down to Start at Top of List with Indirect using Data Validation

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Question Getting Drop Down to Start at Top of List with Indirect using Data Validation

    In the attached workbook, I have a worksheet called "Storage_Info" that has a column entitled "Container Size" (which I have highlighted in yellow). Each cell in this column contains a Data Validation list that is associated with a formula that uses the INDIRECT function. The INDIRECT function refers to four Dynamic Named Ranges in the worksheet named "Input_2_Hidden", which refers to data in worksheets called "Inventory" and "Input_1_Hidden." The list works great, but it starts at the bottom of the list. I've tried using OFFSET to avoid this but it isn't working and I've spend a better part of a day trying to figure out how to make this work. Any suggestions? I've uploaded the Workbook but am not sure that it is attached. Thanks in advance!!!!!!!! If not, let me know.

    Thanks,
    Sandy
    Attached Files Attached Files

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

    Re: Getting Drop Down to Start at Top of List with Indirect using Data Validation

    I took a different approach to the dropdown lists using pivot tables and offset.

    First the pivot tables. These are set up on the Lookups tab.

    The first pivot table in Column A simply gets a unique list of Section and Row. It is overlaid with a named dynamic range LU_SR =OFFSET(Lookups!$A$2,0,0,COUNTA(Lookups!$A:$A)-1,1). I use this name for the validation.

    The second pivot table in Columns C & D is the same except that it contains the storage container size and for lack of a better choice, the count of the product code. You’re in luck that there is only one product code per container. What this does is assures that the subtotal for count of containers for a section-row is the number of containers for that section-row. We can use this information with the offset command.

    There are two named ranges overlaid on this pivot table:
    LU_CSize =OFFSET(Lookups!$C$2,0,0,COUNTA(Lookups!$C:$C)-1,1)
    LU_Num =OFFSET(LU_CSize,0,1)

    Back on the storage info page, I have a couple of helper cells to help me build the validation. These are in columns F & G. They can be deleted in the final product, but I left them there to help show the logic for the derivation of the validation.

    Column F is =MATCH(A3,(LU_CSize),0) – this finds the row for the section-row. This will be our row offset in the Offset command.

    Column G is =INDEX(LU_Num,F3,1) – this is the number containers for the section-row. This will be the number of rows to return using the offset command.

    Now we come to the initial formula for the validation:
    =OFFSET(LU_CSize,$F$3,0,$G$3,1)
    You start with the range, go down F3 number of rows, over 0 columns and return G3 rows and 1 column.

    Substituting the formulas for F3 and G3 into this command gives us the rather hairy: =OFFSET(LU_CSize,MATCH(A3,LU_CSize,0),0,INDEX(LU_Num,MATCH(A3,LU_CSize,0),1),1).
    However, this formula is defined strictly in terms of Column A, so the helper columns are no longer needed.
    Attached Files Attached Files
    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] Dynamic range with indirect in data validation list doesn't work
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-30-2015, 07:38 AM
  2. Drop Down List (Data Validation) Need to start at current value in list
    By bigmantitus in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-07-2014, 11:14 AM
  3. Data validation from list between start & end hour
    By ElmerS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-31-2013, 04:03 PM
  4. INDIRECT.EXT and List using Data Validation
    By jimmyyap in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-02-2013, 03:12 PM
  5. [SOLVED] Macro to start AFTER choise from validation drop down list
    By rpinxt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2012, 09:26 AM
  6. Vlookup from conditional indirect data validation list
    By mmckay in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2011, 08:54 PM
  7. Indirect formula using Data Validation List of Worksheet Tabs
    By Scott in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2005, 11:10 AM

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