+ Reply to Thread
Results 1 to 2 of 2

Dependent, Dynamic Named Range with Multiple Criteria

  1. #1
    Registered User
    Join Date
    06-11-2014
    Location
    Rob Ford, Ford Nation
    MS-Off Ver
    2010
    Posts
    2

    Dependent, Dynamic Named Range with Multiple Criteria

    I have a table with several columns in Sheet A. On Sheet B, I have a cell with a dropdown list that refers to a named range. This named range is defined as:

    =OFFSET(Table!$E$8,0,0,COUNT(IF(Table[ID1]="","",1)),1)

    Table!$E$8 is the first data point in the column [ID1]. This formula currently retrieves the entire contents of the column, excluding blanks, when used as a data validation dropdown.

    I need to add multiple criteria (two to be exact) to this dynamically defined range such that it only produces a list where the data in ID1 match two other points in two other columns on the table. The multiple criteria is sourced from two prior dependent lists that are already set up.

    I have tried combining INDEX, MATCH, and OFFSET into this formula. I have tried NESTED IF's. I have tried & and AND(). All to no avail.

  2. #2
    Registered User
    Join Date
    06-11-2014
    Location
    Rob Ford, Ford Nation
    MS-Off Ver
    2010
    Posts
    2

    Re: Dependent, Dynamic Named Range with Multiple Criteria

    I almost reached the solution, but still require assistance. I have updated the formula to now compute:

    =OFFSET(OFFSET(Table!$E$8,0,0,1,1),MATCH(Criteria1!$C9&Criteria2!$D9,Plan[Wirecentre]&Plan[Type],0)-1,0,COUNTIFS(Table[Criteria1Range],Criteria1!$C9,Table[Criteria2Range],Criteria2!$D9)).

    The problem here is that it works if the data in the Criteria range is listed in order, but once the data within the criteria range is mixed, it gives a polluted list. It also seems to exclude the last item on the list.
    God we trust, all others must bring spreadsheets.

+ 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 Named Range to Auto-Populate another range based on criteria
    By BeachRock in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2014, 06:46 PM
  2. [SOLVED] CountIF for dynamic length column using criteria of Named Range
    By saber0091 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2013, 02:04 PM
  3. Build a Dynamic Named Range from criteria.
    By sinspawn56 in forum Excel General
    Replies: 8
    Last Post: 01-15-2013, 04:40 AM
  4. Count of Dynamic Named Range with more criteria
    By Wermeael in forum Excel General
    Replies: 3
    Last Post: 08-29-2010, 02:42 PM
  5. Dynamic named range across multiple sheets
    By babycody in forum Excel General
    Replies: 3
    Last Post: 07-24-2005, 02:05 AM

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