The add-in defines a custom function that returns a catalog price. The
formula in the cell is "=CAT05("1234", Price). When the user that created
the file opens the file, all is well. When another user opens the file we
have problems. Even though all of our users have the same add-in, the file
worksheet will try to reference the creators add-in, i.e. in the non
creators display the cell say "='C:\Documents and Settings\User\Application
Data\Microsoft\AddIns\CustomTools.xla'!CAT05("1234", Price)", where "User"
is the file creator. So the cell returns the "#NAME?" error.

I have been using find and replace to delete the path of the originator and
just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So my
question is what do I need to do, either when the file is created or when it
is reopened, so the path is not inserted into the formula?

Alan Ibbotson wrote...
>The add-in defines a custom function that returns a catalog price. The
>formula in the cell is "=CAT05("1234", Price). When the user that created
>the file opens the file, all is well. When another user opens the file we
>have problems. Even though all of our users have the same add-in, the file
>worksheet will try to reference the creators add-in, i.e. in the non
>creators display the cell say "='C:\Documents and Settings\User\Application
>Data\Microsoft\AddIns\CustomTools.xla'!CAT05("1234", Price)", where "User"
>is the file creator. So the cell returns the "#NAME?" error.
>
>I have been using find and replace to delete the path of the originator and
>just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So my
>question is what do I need to do, either when the file is created or when it
>is reopened, so the path is not inserted into the formula?

If this add-in is meant to be used by multiple users, then put it in a
shared locations that would always have the same pathname, e.g., put it
into

C:\Documents and Settings\All Users\Documents

or some reasonable *common* subdirectory.

I have tried this in the past but when installing the add-in you are
prompted to copy the add-in to the local drive in the AddIns folder. Even if
I could figure this out that would not be a complete solution. I have
several users that work in other countries and don't have access to our
server.

"Harlan Grove" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Alan Ibbotson wrote...
>>The add-in defines a custom function that returns a catalog price. The
>>formula in the cell is "=CAT05("1234", Price). When the user that created
>>the file opens the file, all is well. When another user opens the file we
>>have problems. Even though all of our users have the same add-in, the file
>>worksheet will try to reference the creators add-in, i.e. in the non
>>creators display the cell say "='C:\Documents and
>>Settings\User\Application
>>Data\Microsoft\AddIns\CustomTools.xla'!CAT05("1234", Price)", where "User"
>>is the file creator. So the cell returns the "#NAME?" error.
>>
>>I have been using find and replace to delete the path of the originator
>>and
>>just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So
>>my
>>question is what do I need to do, either when the file is created or when
>>it
>>is reopened, so the path is not inserted into the formula?
>
> If this add-in is meant to be used by multiple users, then put it in a
> shared locations that would always have the same pathname, e.g., put it
> into
>
> C:\Documents and Settings\All Users\Documents
>
> or some reasonable *common* subdirectory.
>

Alan Ibbotson wrote...
>I have tried this in the past but when installing the add-in you are
>prompted to copy the add-in to the local drive in the AddIns folder. Even if
>I could figure this out that would not be a complete solution. I have
>several users that work in other countries and don't have access to our
>server.
>
>"Harlan Grove" <(E-Mail Removed)> wrote in message
....
>>C:\Documents and Settings\All Users\Documents

The drive/directory path above is *LOCAL* to *EVERY* PC running Windows
2000 or later (unless your IT department has gotten creative with
directory naming). That is, *EVERY* PC running Windows 2000 or later
will have this directory as well as

C:\Documents and Settings\<UserNameHere>

on <UserNameHere>'s PC.

If this doesn't work for you, use a common directory in the root of the
C: drive, e.g., C:\OurWonderfulAdd-Ins. Create that directory as part
of the installation process and put the add-in XLA there.