A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

External workbook references with variable components ??



 
 
Thread Tools Display Modes
  #1  
Old August 10th, 2004, 06:35 AM
lutts
external usenet poster
 
Posts: n/a
Default External workbook references with variable components ??

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..........
  #2  
Old August 10th, 2004, 06:55 AM
papou
external usenet poster
 
Posts: n/a
Default External workbook references with variable components ??

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



  #3  
Old August 10th, 2004, 08:06 AM
Jenny
external usenet poster
 
Posts: n/a
Default External workbook references with variable components ??

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


-----Original Message-----
Use INDIRECT ie:
=3DINDIRECT("'[BSG AGED DEBTOR ANALYSIS 2005.xls]" & A1=20

& "'!D11")
(assuming sheet reference in A1 of current sheet)

HTH
Cordially
Pascal

"lutts" a =E9crit dans le=20

message de
...
In Excel 2002, I am referencing data from an external=20

worksheet via the
following formula:

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

I would like to make the "Jul-04" part variable based=20

on data entered to
the
current worksheet. If I specify, say "Aug-04", I would=20

like to extract
data
from the "Aug-04" tab of the external worksheet, and so=20

on..........


.

  #4  
Old August 10th, 2004, 08:33 AM
Arvi Laanemets
external usenet poster
 
Posts: n/a
Default External workbook references with variable components ??

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





  #5  
Old August 11th, 2004, 03:31 AM
lutts
external usenet poster
 
Posts: n/a
Default External workbook references with variable components ??

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

"lutts" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking to an external variable file name Kelly Worksheet Functions 2 February 12th, 2004 04:00 PM
External References Pinda Worksheet Functions 1 November 10th, 2003 08:55 PM
External References Pinda Worksheet Functions 2 November 10th, 2003 03:52 PM
Formula references when copying a workbook Charlie Worksheet Functions 0 November 5th, 2003 02:41 PM


All times are GMT +1. The time now is 05:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.