Named Range in Different Workbook

[lydiapenrose]

Hello!
I have an excel workbook and I would like to define named ranges for this workbook in another workbook. The first workbook is a template that many people will use and the named ranges will change so I figure changing the list excel (workbook 2) will be easier than giving everyone a new template every time. So the List workbook with the named ranges will be kept in the same folder as the original template, but I am not sure of the actual location as many people are going to be using this template. How can I name the ranges using a relative reference for an unopened workbook located in the same folder?

Thanks in advance for your help!

 

[lydiapenrose]

In case my first post was confusing, I basically want one workbook to take the defined named ranges from another workbook using relative referencing.

Any help would be appreciated. Thanks!

 

[mikerickson]

Does this help

MsgBox Workbooks("otherBook.xls").Names("someRange").RefersToRange.Address(,,,True)

Workbooks("otherBook.xls").Names.Add Name:="NameInOtherBook", RefersToR1C1:= "=Sheet1!R3C3"

ThisWorkbook.Names.Add Name:="NameInHereRangeInOtherBook", RefersToR1C1:= "=[OtherBook.xlsm]Sheet1!R3C3"

 

[lydiapenrose]

I'm looking for a formula that I could type into the name manager to define named ranges as a range in a different workbook that won't necessarily always be in the same location.

 

[mikerickson]

I don't understand what you are trying to do
You have Workbook1 and Workbook2

Do you want to define a range in Workbook1 that refers to cells in Workbook2?
Typing [Workbook2.xls]Sheet1!$A$1:$D$5 in the Define Name box would do that.

 

[lydiapenrose]

Oh! Yes. That does answer my question.

Thanks so much! And Happy New Year!

 

[lydiapenrose]

Actually, after I do that, save the workbook, close it and open it again, the named ranges are then not relative. They change to the location of the second workbook as it is on my pc but it will be in a different location (ex: not necessarily the D drive) when other people use the workbooks. Is there a way to keep it as a relative reference?

 

[mikerickson]

No.
The should be a Find-The-Link dialog that opens if the file has been moved.