+ Reply to Thread
Results 1 to 6 of 6

Dynamic Named Range doesn't return any values - help!

  1. #1
    Registered User
    Join Date
    08-15-2016
    Location
    Leeds, England
    MS-Off Ver
    2013
    Posts
    14

    Dynamic Named Range doesn't return any values - help!

    I am trying to create a dynamic named range using a list in Excel, for a drop-down list. Currently the list looks like this:

    Oranges
    Apples
    Bananas
    0
    0
    0
    0
    and so on

    The 0's are because these cells contain formulas to pull these values from another spreadsheet, which returns 0 if it runs out of values to return. A blank would also be acceptable but that's an extra formula to write.

    I have created a drop-down list on another tab using this list. I want the drop-down to only show the non-0 values (i.e. Oranges, Apples, Bananas only, no 0's). But if the external sheet to which this formula is linked to changes, so another fruit is added, the named range extends automatically without external intervention. So if Blueberries is added to the external sheet, the drop-down list will automatically include Blueberries underneath Bananas.

    Numerous searches of these forums and others provided me with this solution:
    OFFSET($A$2,0,0,COUNTA($A:$A)-COUNTIF($A:$A,0)-1,1)
    which I put as the "Refers To" for the named range.
    Where A is the column the fruits are listed in and A1 contains the header, which I don't want in the list. This is counting the populated cells in the column and subtracting the number containing 0, plus 1 more for the header.

    I built the formula in a cell first to check it works. It does - if I highlight the formula and press F9, it shows me {"Oranges";"Apples";"Bananas"} - no 0's - showing that the array formula is returning the right values.

    But when I put this same formula into the Name Manager as a new named range, it doesn't return any values. In the Value column of Name Manager, where the other (non-dynamic) named ranges show the lists of values they refer to, I just get {...}. If I use this named range as a drop-down list using Data Validation, no drop-down box appears when I click on the arrow (and Data Validation warns me there is an error).

    What on earth am I doing wrong? I can't find any reference to this issue on the web anywhere. Please help! This is driving me nuts. I am a bit of a VBA novice so I'd prefer not to use it, but if needs must I'll give it a go!
    Last edited by nickersonpower; 11-21-2016 at 01:01 PM. Reason: Solved

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Dynamic Named Range doesn't return any values - help!

    Dynamic named range and dynamic data validation don't see eye to eye which could be causing the issue.

    A sample workbook in this instance would be helpful to show you what can be done for what you're asking for.

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    902

    Re: Dynamic Named Range doesn't return any values - help!

    Would you be able to upload a sample?

    Quick question, when the blueberries is added, would it be right below Bananas or would it be a at least 1 or more zero (0) and/or blanks before the fruit "Blueberry"?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,928

    Re: Dynamic Named Range doesn't return any values - help!

    I think you need to include the sheet name before the cell references in your OFFSET formula, otherwise Excel will not know which sheet it refers to.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    08-15-2016
    Location
    Leeds, England
    MS-Off Ver
    2013
    Posts
    14

    Re: Dynamic Named Range doesn't return any values - help!

    Hi all, thanks for all your quick replies! I have prepared a sample workbook, however for some reason the webpage won't let me upload it - when I click on the paperclip icon, I just get a blank box underneath it. I've tried both Chrome and IE, to no avail...

    I've noticed that Excel automatically puts sheet references into named ranges, so that's not the issue.

    I have noticed, however, in trying to create a sample workbook, that it works fine as long as I don't use INDIRECT to choose the drop-down list.

    In my original file, I have dependent drop-downs, so in the Data Validation box, I put this:
    =indirect($e$7)
    where E7 contains the name of the list. Let's call it Fruit.

    If I just put =Fruit in this box, it works fine. So even though Name Manager doesn't give me the right values, it still works as a drop-down. However, if I use INDIRECT to choose the list in the drop-down, it doesn't work.

    Thoughts? Again, sorry I can't upload the sample workbook, I did try!

  6. #6
    Registered User
    Join Date
    08-15-2016
    Location
    Leeds, England
    MS-Off Ver
    2013
    Posts
    14

    Re: Dynamic Named Range doesn't return any values - help!

    Hi again, I fixed it!
    I used EVALUATE, see this webpage:
    http://www.accountingweb.co.uk/any-a...irect-function
    Was a bit much to get my head around, but we got there in the end! Turns out INDIRECT and Data Validation do NOT mix with dynamic named ranges until you get this baby involved...
    Thanks again for all your help with my very first post!

+ 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. Dynamic Named Range with Unique Values Only for Sumproduct Function
    By tlscowden in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2015, 03:16 PM
  2. change cell values of named dynamic range in transfer
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2014, 04:44 PM
  3. Dynamic named range that ignores formulas that return blank
    By Nils88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2013, 05:02 AM
  4. [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
  5. Way to retrieve values from a dynamic named range?
    By OLDWEASEL in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-08-2011, 09:53 AM
  6. dynamic named range relating to cell values
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2008, 06:57 AM
  7. paste values from named dynamic range to another worksheet
    By Nate H in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2006, 02:50 PM

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