+ Reply to Thread
Results 1 to 12 of 12

Having trouble with a dynamic dropdown and dynamic named range

  1. 11-11-2021, 11:09 AM #1
    Ryan_M14
    Ryan_M14 is offline
    Registered User
    Join Date
    11-11-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    5

    Having trouble with a dynamic dropdown and dynamic named range

    I'm trying to create a dynamic dropdown that references a cell which contains the name of my dynamic ranges. For the example the named range is called NAMED. If I type =NAMED into a cell it populates my list, or if I go into data validation-list =NAMED I get a list. What I'm trying to do in data validation is =Indirect(b4) where b4 would contain NAMED but this is giving me an error and the dropdown is not populating.

    My dynamic ranges are made in the name manager using =OFFSET(sheet1!k3:K16,0,0,counta(k3:k16)) and as previously mentioned if I type =NAMED anywhere I get the appropriate list. Thank you in advance.
    Attached Files Attached Files
    Last edited by Ryan_M14; 11-11-2021 at 11:47 AM.
    Reply With QuoteRegister To Reply
  2. 11-11-2021, 11:45 AM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Having trouble with a dynamic dropdown and dynamic named range

    You could use a table to store the list and create the list by using =Table1[NAMED].

    See attached.

    BSB
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 11-11-2021, 11:46 AM #3
    Ryan_M14
    Ryan_M14 is offline
    Registered User
    Join Date
    11-11-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    5

    Re: Having trouble with a dynamic dropdown and dynamic named range

    That works perfect. Thank you.
    Reply With QuoteRegister To Reply
  4. 11-11-2021, 11:48 AM #4
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Having trouble with a dynamic dropdown and dynamic named range

    Happy to help

    BSB
    Reply With QuoteRegister To Reply
  5. 11-11-2021, 12:22 PM #5
    Ryan_M14
    Ryan_M14 is offline
    Registered User
    Join Date
    11-11-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    5

    Re: Having trouble with a dynamic dropdown and dynamic named range

    Quote Originally Posted by BadlySpelledBuoy View Post
    Happy to help

    BSB
    Hey I thought that fixed it but my sample wasn't good enough to replicate the entire problem, if you wouldn't mind looking at this updated sample I'd really appreciate it. Essentially I have a list with blanks throughout and creating a named ranged with only the populated cells. I thought if the formula posted it into a table(like your previous solution) it would overcome my problem.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 11-11-2021, 01:07 PM #6
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Having trouble with a dynamic dropdown and dynamic named range

    Would VBA be an option?

    BSB
    Reply With QuoteRegister To Reply
  7. 11-11-2021, 01:14 PM #7
    Ryan_M14
    Ryan_M14 is offline
    Registered User
    Join Date
    11-11-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    5

    Re: Having trouble with a dynamic dropdown and dynamic named range

    Yes I'm just not sure where to begin with it for this situation.
    Reply With QuoteRegister To Reply
  8. 11-11-2021, 01:19 PM #8
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Having trouble with a dynamic dropdown and dynamic named range

    Actually, no need for VBA.

    In the attached version I've used the below formula to create a list without blanks in column P (and a similar one for the other list)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Then to create the dynamic named ranges I've used this
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This seems to work
    Note, this probably won't work outside of Excel365.

    BSB
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 11-11-2021, 01:26 PM #9
    Ryan_M14
    Ryan_M14 is offline
    Registered User
    Join Date
    11-11-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    5

    Re: Having trouble with a dynamic dropdown and dynamic named range

    You are awesome, thank you so much.
    Reply With QuoteRegister To Reply
  10. 11-11-2021, 01:28 PM #10
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Having trouble with a dynamic dropdown and dynamic named range

    No problem at all. Glad I could assist.

    BSB
    Reply With QuoteRegister To Reply
  11. 11-11-2021, 01:34 PM #11
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Having trouble with a dynamic dropdown and dynamic named range

    Another solution keeping the desired order.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by DJunqueira; 11-11-2021 at 01:36 PM.
    Reply With QuoteRegister To Reply
  12. 11-11-2021, 01:43 PM #12
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Having trouble with a dynamic dropdown and dynamic named range

    You wouldn't even need that formula. If you wanted to maintain the order you could just remove the SORT function from my earlier formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    I added it because potentially in the real workbook the list would be a lot longer and it's easier to find the required value in a sorted list.

    BSB
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic named ranges in Chart is dropped when last value is selected from dropdown list
    By Dbroek in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-03-2020, 03:54 AM
  2. [SOLVED] Dynamic Named Range: Trouble Omitting Multiple Row Heading
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-09-2017, 12:32 AM
  3. Named formula (dynamic named range) used in chart SERIES may not begin with C or R
    By 6StringJazzer in forum Tips and Tutorials
    Replies: 0
    Last Post: 03-08-2016, 02:25 PM
  4. [SOLVED] Clear dynamic named sheet, compare cells from 2 sheets, copy all matched rows to dynamic s
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-15-2015, 12:31 PM
  5. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM
  6. Trouble with Dynamic Range
    By yaju1120 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2007, 09:59 AM
  7. [SOLVED] Trouble with dynamic named range
    By vermutmb@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2006, 06:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1