+ Reply to Thread
Results 1 to 9 of 9

Analysis Tool Pak installed but #NAME still showing

  1. #1
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Analysis Tool Pak installed but #NAME still showing

    Hi,

    I have encountered a weird problem

    I have a cell which uses the formula
    Please Login or Register  to view this content.
    where 'holidays' is a dynamic range name. The formula used to evaluate correctly.

    I sent the workbook to someone else who encountered a problem and found that this cell evaluated to #Name. We eventually identified the problem in that he did not have the Analysis Tool Pak VBA add in installed. Once he'd installed it the problem disappeared - apparently.

    In identifying the problem, at some stage I unticked my Analysis Tool Pak add in to prove that I got the same problem as he did - which I did. Once we'd proved that I again ticked the Add in box.

    However I now find that when I load the workbook, although the cell evaluates correctly on initial opening, as soon as I copy the formula from the equivalent formula in the line above, it evaluates to #Name. However if I F2 to open the edit and then immediately enter the value back again, it evaluates correctly.

    I've done all the obvious things like re-booting but nothing seems to overcome this problem with the copying part of the process.

    Can anyone offer any clues...

    Usual TIA
    Last edited by Richard Buttrey; 09-10-2009 at 02:33 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analysis Tool Pak installed but #NAME still showing

    Hi,

    Good idea. I tried using a standard match function but that exhibits the same problem. At least on my machine. I'm told by someone to whom I sent the file that there's still works. The only difference I'm aware is that previously I unselected the Add In and then reselected it.

    I'm attaching the file here.

    The relevant column is column V on the Data tab. that's the one with the Network Days. Celles which evaluate to NOT 'A', i.e those which should return a number (of days) are the problem cells - at least for me. This file I'm sending has them all evaluating as #Name, but i suspect when you open it, assuming you have the Analysis Tool pak VBA selected, that you may not have the same problem.

    I'll be interested to hear what you see.

    Kind regards
    Attached Files Attached Files
    Last edited by Richard Buttrey; 09-10-2009 at 10:20 AM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analysis Tool Pak installed but #NAME still showing

    Hi Richard, I received your PM and see that you have inadvertently written over my previous post...

    I opened your file and as you said, I see #NAME errors in random cells, not in all cells in Column V.. it seems the error shows only in cells where the TRUE part of the IF() statement should be evaluated....

    ... It seems that it is conflicting somehow with the Holidays dynamic named range...

    I tried creating another named range called HolidaysA and made it a defined range... and then adjusted the formula in column V accordingly, and the #Name errors disappeared...

    ... so maybe because Networkdays() is not a function that works with formulas within data validation, conditional formatting and probably named formulas... then somehow, even though it is indirectly involved in the holidays named range... it is getting confused and throwing you that error.... if you use a defined named range, it doesn't throw the errror.... at least that is what I have gathered... does it make sense?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Analysis Tool Pak installed but #NAME still showing

    .. so maybe because Networkdays() is not a function that works with formulas within data validation, conditional formatting and probably named formulas.
    Pre 2007 I didn't think you could use any ATP functions in CF / Validation etc... (ie seen as external references - in 2007 the ATP functions are "standard").

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analysis Tool Pak installed but #NAME still showing

    I think that's what I said....

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Analysis Tool Pak installed but #NAME still showing

    Yep - sorry I missed out the all important *because* when I read the (quoted) line in your post ! doh - sorry !

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Analysis Tool Pak installed but #NAME still showing

    Hi,

    Yes that seems to make sense. I've done as you suggested and converted the name 'holidays' to a "normal" name, i.e. not a dynamic name.

    What's weird, and what I don't really understand though is this was working fine without any problems using the dynamic name. I had occasion to send it to someone else who encountered the problem. In his case it was because the Add In wasn't installed. When he added it it seemed to cure his problem, but maybe he'll have the problem when as part of a month end routine the formula gets copied down.

    As part of tracking down the problem, I decided to deselect the add in to see if I could replicate his original problem - which I did and I did indeed encounter his problem. So I thought Aha! Excellent, Sorted. But when I came to reselect the add in, expecting the situation to revert to the normal non problematic behaviour I'd been used to, I then started getting this problem.

    However many thanks for taking a look, and I at least have a work around.

    Regards

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analysis Tool Pak installed but #NAME still showing

    Maybe it could be version sensitive too...who knows? Are all the people you've sent it to working on same versions of Excel?

  9. #9
    Registered User
    Join Date
    07-06-2009
    Location
    NH, USA
    MS-Off Ver
    2010 & 2013
    Posts
    38

    Re: Analysis Tool Pak installed but #NAME still showing

    I've seen similar strange behavior with other tool pak commands. (I think it was ISODD and ISEVEN.) The original spreadsheet was made in Office 2007, but for a while, users could open the sheet in Excel XP and it would work fine. All of a sudden, it just stopped working. Nothing I did seemed to bring back the old functionality.

    Of course, I regard this as a good thing. It got the boss to spring for Office 2007 on more machines.

    I wonder if I should tell them I run Office 2010 at home... Nah!

+ 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