+ Reply to Thread
Results 1 to 10 of 10

#NAME? help

  1. #1
    Registered User
    Join Date
    01-08-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    Office 365
    Posts
    13

    #NAME? help

    Good afternoon all,


    Currently have an issue with an excel Macro file where some users get a #NAME? error for formula cells. But when the same document is sent to myself/others the error is not shown and the data is displayed correctly. The cells are locked so that users are not able to edit the formulas. The file is also saved as a Macro file (due to some of the formulas that are built and the protection put in place). Not sure if that would have something to do with this error popping up.

    Below is what the user with the issue sees
    name - error image.JPG


    Below is what I show when pulling the same spreadsheet.
    Name - non error Image.JPG

    All of the users are using the same version of Excel. I would attach a sample but I have not been able to replicate this issue, and it would be hard for me to provide a real world example excluding confidential information.

    Thank you for your help as always.
    Attached Images Attached Images
    Last edited by 6StringJazzer; 12-05-2019 at 12:41 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: #NAME? help.

    In this case pictures are useless.

    If you can't attach a file that shows this error, you have to at least provide the formula that is returning the error. If the formula uses a UDF then you have to show the code for the UDF.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: #NAME? help.

    Is everyone using the same version of Excel with the same Regional Settings?

    See the yellow banner at the top of the page.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    01-08-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    Office 365
    Posts
    13

    Re: #NAME? help.

    Thank you for the responses. As far as I can tell all of the users are using the same version and same regional settings.

    I believe I was able to remove the confidential information and provide an example of the spreadsheet that users have experienced issues with. I removed the passwords so that the you can view the formulas. Hopefully this is helpful.
    Attached Files Attached Files

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: #NAME? help.

    I am not getting the errors so I can only offer educated guesses.

    First, the NAME error occurs when you use a name in a formula that Excel does not recognize. There are a couple of ways that one user can get this but another does not. It could be a function name that exists in the current version of Excel but not earlier versions (like CONCAT), or a function from an add-in that one user has but another does not.

    The cells in row 20 have a name error because the cells in row 12 have a NAME error. The cells in row 12 have a NAME error most likely because they refer to cells on sheet "9" that have NAME errors. Those cells on sheet "9" have many precedents on that sheet. Any one of them could be causing the problem, so they need to be traced back. However, sheet 9 is protected with a password so I cannot view all the cells that are referenced, such as hidden columns I, O, U, AM, AS.

    I could help if you give me the password, but you should be able to do that tracing yourself. You need to walk backwards from the formula until you find a cell that gives a NAME error but does not reference any other cells that have a NAME error. When you find it, it will probably have a function name that does not exist in everybody's version of Excel.

  6. #6
    Registered User
    Join Date
    01-08-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    Office 365
    Posts
    13

    Re: #NAME? help.

    I will begin to dig into the path of the formulas to see what might be causing the error. I thought i had removed all of the passwords. But if you want to look into this issue also below is the password.


    Password:grind
    There is no spaces or capital letters.

    Really appreciate the responses.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: #NAME? help.

    You are using function IFS. This function was introduced in Excel 2019 and 365. In earlier versions it will produce a NAME error. If you are using 2013 like your profile indicates, it won't work for you (is your profile up to date?). This is almost certainly the problem, because your formulas are very simple and this is the only function you are using in this precedent chain.

    I am not sure why emailing it to people would avoid the error but maybe Excel preserves previously calculated values and will produce the NAME error only on the next sheet calculation. If someone gets the error, but not in the version that is emailed, I suggest hitting F9 to force recalculation to see if the error occurs. (That doesn't solve it, just confirms the diagnosis.)

    A workaround is to rewrite your formulas to use IF. Another workaround would be to write VBA for the IFS function to guarantee it is there, but that is not trivial to write due to the dynamic number of arguments.

  8. #8
    Registered User
    Join Date
    01-08-2018
    Location
    Minneapolis, MN
    MS-Off Ver
    Office 365
    Posts
    13

    Re: #NAME? help.

    Thanks 6StringJazzer! This solved the problem on where to look for what was causing this issue. I am using 365 (updated my profile), and have confirmed some of the users are on older versions of Excel. I will explore changing the formulas or getting users to update to a more current version of Excel.

    Hope you have a great day!

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: [SOLVED] #NAME? help.

    FWIW, IFS is an inefficient function as it evaluate all its arguments regardless of whether they are used.
    Rory

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: [SOLVED] #NAME? help.

    jhamm2018, I see you have manually edited your title to add "[SOLVED]". The correct way to marked a thread SOLVED is to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..." I have fixed this since you're new.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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