+ Reply to Thread
Results 1 to 6 of 6

Dynamic Named Range not recognized in formulas

  1. #1
    Registered User
    Join Date
    05-21-2019
    Location
    Boise, Idaho, USA
    MS-Off Ver
    Office 365
    Posts
    4

    Dynamic Named Range not recognized in formulas

    I am having a problem with a formula/formulas not recognizing a named range in the formula. Specifically, the "refer to" in the named range creates a list of tabs in my workbook. I could give more specifics about that, but I suspect my solution is elsewhere, because my colleague does not have the same problem... when he sends me a file I can see it is working properly, but once I press the "Enable Editing" button the formulas all revert to #NAME? errors. Also, if I create a named range, and use it in a formula it works. But when I save, close, and re-open, the same thing happens... all formula referencing that named range change to #NAME? error. Is there a setting I need to change?

  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
    47,095

    Re: Dynamic Named Range not recognized in formulas

    Please attach the 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
    05-21-2019
    Location
    Boise, Idaho, USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Dynamic Named Range not recognized in formulas

    I am new. I cannot see how/where to attach file.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,865

    Re: Dynamic Named Range not recognized in formulas

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  5. #5
    Registered User
    Join Date
    05-21-2019
    Location
    Boise, Idaho, USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Dynamic Named Range not recognized in formulas

    Ali,in attached file I re-created the problem. (Orig file too big.)
    You can see in column B of the first tab is where I show 3 rows with a named range having the #NAME? errors.
    Below that are 3 simple named ranges (I say simple because they have simple Excel formulas as the "refer to" in the named range definition.) You can see they work just fine.
    Now, I want to add, that you MAY find that the 3 rows on top (I think rows 2-4) work for you.
    If so, you may be at a loss to help. In the orig file with the problem, my colleague did not have a problem, but once I saved/closed/re-opened the big orig file it created the #NAME? error. Same happened on this attached file.
    Also, a good file my colleague sent me looked good (i.e. no #NAME? errors), until I clicked on "Enable Editing", then those pesky errors occurred again.
    Why my computer? A setting wrong or what?
    Thx, S. Lewis
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,865

    Re: Dynamic Named Range not recognized in formulas

    Upon downloading and 'Enable Editing' I got those #NAME? errors as well. There was also a SECURITY WARNING Macros have been disabled --- with an option to 'Enable Content'. (The Named formula TabList contains an old Excel macro function called GET.WORKBOOK.) When I click on 'Enable Content' the errors vanish and the sheet names appear.

    Is there any chance Macros have been disabled in your Excel settings?

+ 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 not recognized in formulas.
    By HuskerBronco in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-21-2019, 09:52 PM
  2. Dynamic Named Range added to formulas
    By DJG7680 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-03-2018, 03:33 AM
  3. Why is a named range not recognized
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2017, 02:24 PM
  4. Replies: 0
    Last Post: 03-08-2016, 02:25 PM
  5. Dynamic Named Range COUNT ignore formulas
    By joel@reif-wa.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2013, 08:06 PM
  6. 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
  7. Array formulas referencing a named range or named table
    By anrichards22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2013, 11:59 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