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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |