Using path names within the INDIRECT function

[LeighAnne]

Hi Everyone !

I want to use the INDIRECT function to find a reference in another workbook, but I can't seem to get it to work when using paths.

For Example:

WorkbookXXX cell A1 has the entry:
"C:\My Documents\[WorkbookYYY.xls]Sheet1'!A1"

I want to put the value of "C:\My Documents\[WorkbookYYY.xls]Sheet1'!A1" into another cell in WorkbookXXX.

I've tried the basic "=INDIRECT(A1,TRUE)" but it gives me a #REF! error.

Any ideas ?

 

[LSTAN]

Hi

There is no need to use the INDIRECT function. Simply

='C:\My Documents\[Book1.xls]Sheet1'!$A$1

When you open the WorkbookXXX you will be prompt when you want to update the data from WorkbookYYY (if there is changes made) or leave the data as it is.

Hope this helps.
LSTAN

 

[LeighAnne]

LSTAN,

Your right of course but maybe I should explain the larger problem.

A matrix is set up using the following headings:

Column A = Employee Names
Column B = Team they belong to (each team has a separate workbook)
Row 1 = Week Numbers 1 - 52

I want the data values in columns C to AZ (i.e. weeks 1 to 52) to:

1. Open the Team workbook (Dependant on the value in Column B)
2. Lookup the individuals name in this workbook
3. and return the value for that week (column).

I was thinking along the lines of:

If(B="This_Team",LOOKUP(A,C:\My Documents\[Workbook_Team]Sheet1'!A1:AZ53,3,FALSE),If(B="Other_Team" etc. etc.

but as there are 10 teams the path names became too long for the cell.

So I thought that maybe I could put the path names in another cell and refer to them using INDIRECT.

Any help would be exceptionally useful.

Le



So depending on

 

[bitbuster]

Le,

The indirect function returns "#ref" because the remote workbook you are referring to is closed. You could open them all before using the indirect function (e.g. with a button).
Another solution you might use here is to create a little table on a new sheet with the team name in the first column and the path of the corresponding workbook in the second column. This table you can use with a vlookup to return the corresponding path when a team name is given.

regards,

Bitbuster