# locking external references in excel formulas



## bryanth (Apr 16, 2008)

I am having trouble with a formula using an external reference to a cell in another worksheet in another workbook, a template workbook. The formula works fine, my problem is whenever, lets call it workbook2, is saved as a different name (as the company i work for wants it to) the external reference name changes. So far no one really knows what is goin on here. 

Could someone please help, all thoughts and help are welcome.

Thanks

Bryant


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome.

Sounds like the references in your formula are relative rather than absolute. Do you want the name of the template workbook to remain constant? You would need to 'hard code' that into the formula.


----------



## bryanth (Apr 16, 2008)

Ok thank you. Now how exactly would I "hard code" that into the formula. Yes I want the template name to remain constant.

Thanks again,
bryanth


----------



## Glaswegian (Sep 16, 2005)

Can you post an example of your formula?

By 'hard coding' I mean using a set path or filename, rather than assigning those details to a variable.


----------



## bryanth (Apr 16, 2008)

=IF('Cat.XLS'!SN="CATMOR","XESQ9842", IF('Cat.XLS'!SN="CATENG","QSLL","XAB 08112"))

There is an example of the formula I use. Cat.XLS is the name of the template I am using. SN is the name of a cell in the template, its short for short name. Depending on what is in that SN cell, determines what is written in the cells that have this particular formula in them.

i.e. Say on the cat.xls template the SN says CATMOR, the cell with the above formula would have XESQ9842.

I hope this enough information, if not let me know and I would be happy to put more up here.

Thanks again.


----------



## Glaswegian (Sep 16, 2005)

Try inputting the full path to the file in the formula

=IF('*\\server\folder\folder\[Cat.XLS'!]*SN="CATMOR","XESQ9842"......


----------



## bryanth (Apr 16, 2008)

When I use the formula you gave me I get an invalid reference warning message. What exactly do you mean by the server, in the formula. This particular place uses a server, but they gave me a path from the F drive on their main computer where this particular project would be stored when finished.

Do you have any other suggestions for the formula?

It seems that whenever I enclose the template name in brackets it doesn't recognize it.....

Thanks again for your help


----------



## bryanth (Apr 16, 2008)

The current formula is: 

=IF('F:\CHERYL''S\New Templates\[Cat.XLS'!]SN="CATMOR","XESQ04629",IF('F:\CHERYL''S\New Templates\[Cat.XLS'!]SN="CATENG","QSLL","XAB 08112"))

With this formula I'm getting an invalid external reference to a worksheet message.

Could you offer any more advice?


----------



## bryanth (Apr 16, 2008)

Also the full path name doesn't seem to stop the change of the template name.

i.e.

=IF('F:\CHERYL''S\New Templates\Cat.XLS'!SN="CATMOR","XESQ04629",IF('F:\CHERYL''S\New Templates\Cat.XLS'!SN="CATENG","QSLL","XAB 08112"))

this is the original formula but when you save the template (Cat.XLS) as say CM 0404 the formul becomes this:

=IF('F:\CHERYL''S\New Templates\CM 0404.XLS'!SN="CATMOR","XESQ04629",IF('F:\CHERYL''S\New Templates\CM 0404.XLS'!SN="CATENG","QSLL","XAB 08112"))


----------



## Glaswegian (Sep 16, 2005)

Normally a drive letter is just a reference point. Drive letters can change so I would always use the UNC (Universal Naming Convention). UNCs for Windows will normally be in this format

*\\Server\Shared Folder\Folder*

and so on, where Server is the address of the server in question.

http://compnetworking.about.com/od/windowsnetworking/l/bldef_unc.htm


----------



## bryanth (Apr 16, 2008)

This might be a stupid question but how would I get the server name? Also with that calculation you gave me, would not having the server name be the reason excel says that there is an invalid external reference to a workbook?


----------



## bryanth (Apr 16, 2008)

I asked about the server name, and no one could really tell me. On their computers though they have RoweData on '192.168.1.100' (F and this is their server drive that is on all their computers would this be what I need? If so what part of the name?


----------



## Glaswegian (Sep 16, 2005)

Hmmm..that's a good question. I would have thought a server would need to be named something. Our corporate network name servers based on location, amongst other things, so we have servers named like this

\\xxxx*gl*07 for Glasgow
\\xxxx*ln*02 for London

and so on.

If you can't get that then I'm not sure what the solution would be.


----------

