link to a dynamic range in another excel file

[Rembrandt]

Hi there,

Does anyone know how to refer to a dynamic range in another excel file, without having to open it, using excel 2000...

 

[BigC]

What do you mean by "dynamic range"?
Has the range of cells been given a name (eg. "Sales") in the workbook that is closed (better), or is it just a range of cells (eg. A1:D34)?
Either way, you can just type the formula into the open spreadsheet, viz;

Range has been named:
=+'C:MyWorkbook.xls'!Sales

or

Range has NOT been named:
=+'C:[MyWorkbook.xls]Sheet1'!$A$1

HTH
BigC

 

[Rembrandt]

I am using a named range defined as

OFFSET(MySheet!$A$1;0;0;COUNTA(MySheet!$A:$A);1)

extending automatically to the number of rows used

 

[Andrew Poulsom]

Doesn't the first suggestion by BigC work, using the name assigned to your dynamic range?

 

[Rembrandt]

In case of a dynamic range, Big C's suggestion only works when referring to an open workbook

Please help!!

 

[Andrew Poulsom]

Hmmm, I expect that's because OFFSET is a volatile function.

Can you live with referencing the whole column(s) like this?

='C:[MyWorkbook.xls]Sheet1'!$A:$A