+ Reply to Thread
Results 1 to 9 of 9

Dynamic Range: return values based on other cell value

  1. #1
    Registered User
    Join Date
    11-09-2016
    Location
    London, England
    MS-Off Ver
    2010/2016
    Posts
    66

    Dynamic Range: return values based on other cell value

    Hi,

    Is this possible? I have 2 dynamic ranges, one for Groups and one for Options. They're 'relational':

    Groups (Named Range = "ServiceGroups"):

    Name | ID
    Group 1 | 1
    Group 2 | 2
    Group 3 | 3

    Options (Named Range = "ServiceOptions"):

    Group_ID | Name
    1 | Option 1
    1 | Option 2
    1 | Option 3
    2 | Option 4
    2 | Option 5
    3 | Option 6


    In the database world the relationship would be between Groups.ID and Options.Group_ID.

    I have a Data Validation List drop-down that uses ServiceGroups Named Range for it's values and depending on what group is chosen, this should show the various options belonging to that group in another cell drop-down. For example if somebody selects Group 1 from the first drop-down, the 2nd drop-down should show Options 1, 2, 3

    If I set the 2nd drop-drown to use ServiceOptions for it's values it obviously show all the options, so I need to adjust the Named Range to be adaptive to the value in the first drop-down.

    Never done this before.

    Thanks

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,482

    Re: Dynamic Range: return values based on other cell value

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    11-09-2016
    Location
    London, England
    MS-Off Ver
    2010/2016
    Posts
    66

    Re: Dynamic Range: return values based on other cell value

    In the process of redacting it first

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,482

    Re: Dynamic Range: return values based on other cell value

    Of course!

  5. #5
    Registered User
    Join Date
    11-09-2016
    Location
    London, England
    MS-Off Ver
    2010/2016
    Posts
    66

    Re: Dynamic Range: return values based on other cell value

    OK, I've really simplified it.

    Pricing sheet has just the 2 bits, and the Data sheet shows the relationships.
    Attached Files Attached Files

  6. #6
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,768

    Re: Dynamic Range: return values based on other cell value

    Please try data validation list at D6

    =OFFSET(Data!$J$4,MATCH(VLOOKUP(D5,Data!$F$5:$G$7,2,),Data!$I$5:$I$9,),,COUNTIF(Data!$I$5:$I$9,VLOOKUP(D5,Data!$F$5:$G$7,2,)))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-09-2016
    Location
    London, England
    MS-Off Ver
    2010/2016
    Posts
    66

    Re: Dynamic Range: return values based on other cell value

    Thanks so much!

    Is it possible to use Dynamic Ranges within that, instead of absolute cell references? This breaks it - interestingly it works OK with just a named range as the first parameter for VLOOKUP (the 'ServiceGroup' one)

    =OFFSET(Data!$J$4,MATCH(VLOOKUP(ServiceGroup,ServiceGroupNames,2,),ServiceOptionNames,),,COUNTIF(ServiceOptionNames,VLOOKUP(ServiceGroup,ServiceGroupNames,2,)))

    This is what ServiceGroupNames is: =OFFSET(Data!$F$4,1,0,COUNTA(Data!$F:$F)-2,1)

    Thanks

  8. #8
    Registered User
    Join Date
    11-09-2016
    Location
    London, England
    MS-Off Ver
    2010/2016
    Posts
    66

    Re: Dynamic Range: return values based on other cell value

    Worked it out. My named ranges were wrong.

    Thanks again

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,482

    Re: Dynamic Range: return values based on other cell value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Return values to specific columns based on range of cell values
    By sbrt10 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-27-2019, 03:15 AM
  2. [SOLVED] Populate Dynamic Array from Cell Values and write to Dynamic Range
    By TFiske in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2018, 09:09 AM
  3. [SOLVED] Dynamic Named Range doesn't return any values - help!
    By nickersonpower in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-21-2016, 01:01 PM
  4. [SOLVED] Return specific values based on the range in another cell
    By AndrewBondarchuk in forum Excel General
    Replies: 3
    Last Post: 11-28-2012, 12:34 PM
  5. [SOLVED] Dynamic Named Range Help - Range Based on Values in Column
    By Filibuster in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-03-2012, 11:13 AM
  6. Formula To Return Values in a Dynamic Cell
    By donnydorko in forum Excel General
    Replies: 3
    Last Post: 10-21-2009, 12:45 PM
  7. Set Dynamic Range based on values
    By cap09 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2009, 11:50 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