External workbook references with variable components ??

[Guest]

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..........

 

[papou]

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

HTH
Cordially
Pascal

 

[Jenny]

Wow, thanks guys. I was just reading through the messages
and you have solved a problem for me that I didn't even
know I had!
I have learnt so much from you all, just from reading
other peoples problems, that I would like to say a big
thank you, and keep it up.
Kind regards
Jenny

-----Original 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" <[email protected]> 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..........


.

 

[Arvi Laanemets]

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)

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

HTH
Cordially
Pascal

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..........

 

[Guest]

Thanks Pascal,

Your solution worked first time. I think I tried that option, but must have
had the syntax wrong.

Anyway, thanks again.

Thanks also to Arvi for his comments. A little too complex for a simple
soul such as me, but interesting nevertheless.

Thanks guys............