+ Reply to Thread
Results 1 to 5 of 5

Data Validation & Dynamic named ranges - full list not showing

  1. #1
    Forum Contributor
    Join Date
    07-19-2010
    Location
    Huddersfield, UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    167

    Data Validation & Dynamic named ranges - full list not showing

    Morning all, I have a bizarre problem....

    Attached is the simplified sheet I'm working with. I have a named range, which is dynamic based on the first data validation list on the first sheet (Matrix) - this changes the named range as needed to one of the three lists. When selecting the named range from the name manager window, the marching ants rotate around the correct list, and all the languages. However, the data validation list on the Matrix sheet only shows about a third of these languages - not the full range... I can't figure out why, when the named range seems to be correctly defining the correct list!

    Formula for the named range I'm using:
    Please Login or Register  to view this content.
    Is there a way around this, or a better way to do this? I've tried having the data validation list dependent on the first selection, but as my Types have spaces within them I can't use them as Names, and can't display this text in any other way to show just one word (specification by the client).
    Attached Files Attached Files
    Last edited by dancing-shadow; 07-19-2017 at 06:55 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,150

    Re: Data Validation & Dynamic named ranges - full list not showing

    Missed posted file!!

  3. #3
    Forum Contributor
    Join Date
    07-19-2010
    Location
    Huddersfield, UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    167

    Re: Data Validation & Dynamic named ranges - full list not showing

    The file added is only showing one language out of the list... the actual file I'm working on shows a more - a different amount for each list though. None of the data I've deleted out affects the named range though, so not sure why this is now different...

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,150

    Re: Data Validation & Dynamic named ranges - full list not showing

    Name your named ranges by selecting title in row 1 and then selecting range: Excel will substitute blanks and "-" with underscore in the names.

    in DV in C6

    =List

    Source:

    =INDIRECT(SUBSTITUTE(SUBSTITUTE(C5,"-","_")," ","_"))
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-19-2010
    Location
    Huddersfield, UK
    MS-Off Ver
    Excel 2007, 2010
    Posts
    167

    Re: Data Validation & Dynamic named ranges - full list not showing

    Thank you so much! This is perfect

+ 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. Dependent List Validation from Dynamic Named Ranges
    By freeride in forum Excel General
    Replies: 11
    Last Post: 01-22-2020, 03:07 PM
  2. Replies: 1
    Last Post: 11-08-2014, 12:45 PM
  3. [SOLVED] Dependent data validation with dynamic named ranges
    By Ace_XL in forum Excel General
    Replies: 4
    Last Post: 08-07-2014, 10:02 AM
  4. Replies: 5
    Last Post: 07-28-2014, 11:53 AM
  5. Dynamic Named Ranges and Indirect Data validation INCOMPATIBLE?
    By css0911 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-08-2013, 06:17 AM
  6. Replies: 14
    Last Post: 10-02-2013, 07:39 AM
  7. Data Validation and Dynamic Named Ranges
    By freybe06 in forum Excel General
    Replies: 15
    Last Post: 07-20-2011, 01:18 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