The source file *must* be open for it to work.

Solution: don't use another file as the source! Keep the source in the same
file.

If you must, then create an area that links to the other file:

='C:\TV\[test.xls]Sheet1'!$A1
='C:\TV\[test.xls]Sheet1'!$A2
='C:\TV\[test.xls]Sheet1'!$A3
etc

Then use that range as the source.

Biff

"DocBrown" wrote in message
...
I'm having a problem with Dynamic range for a named list. I define a
dynamic
range for a list that is referenced on a seperate worksheet as follow:

Refers to:
=OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A$4:$A$18),1)

The cell reference in the 'COUNTA' formula is because I have header rows
in
the lists worksheet. A second 'Status' worksheet uses these lists in
validation to create dropdown lists.

When I copy both of the worksheets to another file, the list definition in
the 'Status' worksheet is automatically being updated to continue to point
to
the original source file:

Refers to:
=OFFSET('[template.xls]Lists'!$A$5,0,0,COUNTA('[template.xls]Lists'!$A$4:$A$18),1)

The 'Lists' worksheet seems to have the original definition but is now
'local' (In the Define Name dialog, the worksheet name is visible to the
right of the list name.)

Now if the original file is closed, the lists stop working. What's going
on
and how to I prevent Excel from automatically updating the list
definition???

Thanks for any help,
John S.