named ranges being renamed

[dhx10000]

This is a really frustrating problem happening. I have a bunch of named ranges in my workbook that refer to ranges on separate workbook. For example, here is a formula I use:

Within Workbook1
=COUNTDIFF(WeekDates)

WeekDates is a named range that refers to the range $AB$:$AB within Workbook2

I defined this named range, within workbook 1 to be: =OFFSET([Workbook2.xls]Metrics!$AB$2,0,0,COUNTA([Workbook2.xls]Metrics!$AB:$AB)). This works fine when I test it. But when I close the two files and open them back up again, the below code replaces my named range definitions

='http://myServer/sites/wtbu/umts/barcelona/Action%20Items/metrics/[BarcelonaInternalActions.xls]Metrics'!$AB$2 0 0 'http://myServer/sites/wtbu/umts/barcelona/Action%20Items/metrics/[BarcelonaInternalActions.xls]Metrics'!

When workbook 1 is opened, I have a macro that opens workbook 2 so that the first workbook can use its data.

 

[Aladin Akyurek]

If you want to work with closed books, you might want to try INDIRECT.EXT of morefunc.