Hi

But be aware INDIRECT works only until source workbook is opened - otherwise
you get an error.

When you can't be sure (all) source workbook(s) is/are always opened, you
have to look for another solution. Possible ways:

1. An UDF, capable to read data from closed workbook. I remember references
to such a function in some of Excel NG's.

2. When the number of possible source workbooks/sheets isn't too big, and
workbook/sheet names are known before, you can have links to all source data
on hidden sheets - a sheet for every WorkbookName.SheetName. And you use
INDIRECT to get data from hidden sheets.

3. You have a (hidden) sheet with ODBC query from one source table. A
macro/procedure, started by button or shortcut, or by worksheets Change
event, rewrites QueryTable object and refreshes the query depending the data
you entered into worksheet. Your formula (a simple link - no need for
INDIRECT) returns the data from query result table.

4. A macro/Procedure, started like above, rewrites the link formula
depending the data you entered into worksheet. I.e. you had '[BSG AGED
DEBTOR ANALYSIS 2005.xls]Jul-04'!D11 as formula, you entered 'Aug-04' into
some cell, and the macro rewrites the formula over as '[BSG AGED DEBTOR
ANALYSIS 2005.xls]Aug-04'!D11


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


"papou" cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Use INDIRECT ie:
=INDIRECT("'[BSG AGED DEBTOR ANALYSIS 2005.xls]" & A1 & "'!D11")
(assuming sheet reference in A1 of current sheet)

HTH
Cordially
Pascal

"lutts" a écrit dans le message de
...
In Excel 2002, I am referencing data from an external worksheet via the
following formula:

='[BSG AGED DEBTOR ANALYSIS 2005.xls]Jul-04'!D11

I would like to make the "Jul-04" part variable based on data entered to
the
current worksheet. If I specify, say "Aug-04", I would like to extract
data
from the "Aug-04" tab of the external worksheet, and so on..........