reference to cells in other files: direct vs indirect

[Amedee Van Gasse]

I inherited a behemoth of an Excel file (Excel 2003) that was created
by multiple people in different countries, who may or may not have
left the company. Anyway, I'm on my own.

In one of the sheets, I find the following functions:

Cell U1:
='Site General'!U1

Cell U2:
="["&INDIRECT("u$1")&"]Recurrent (Summary by Unit)"&""

Cell U4 and below:
=IF(ISERROR(INDIRECT(ADDRESS($L4;4;;1;U$2)));0;INDIRECT(ADDRESS
($L4;4;;1;U$2)))
($L4 becomes $L5, $L6,...)

The result of these functions is:
Cell U1:
V5 Spain.XLS
--> this is a file that is (or should be) always in the same directory
as the current file.

Cell U2:
[V5 Spain.XLS]Recurrent (Summary by Unit)

Cell U4 when V5 Spain.XLS is not open:
0
Cell U4 when V5 Spain.XLS is also open:
some value from the tab "Recurrent (Summary by Unit)" in the file V5
Spain.XLS


Now I close Excel and reopen the first file, only that file. I do not
open V5 Spain.XLS.

In W11, a blank cell, I put the following function:
=ADDRESS($L11;4;;1;U$2)
The result of that function is the following text string:
'[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11

In X11 I put the following function:
=INDIRECT(W11)
The result is #REF!

Then I copy W11 and I Paste Special -> Values into W12.
The value of W12 is now:
'[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
=INDIRECT(W12) is also #REF!

Then I click with the mouse inside W12 and I change it from:
'[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
to:
='[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
Now the result isn't #REF! but 123456.7: the value of cell D11 on
sheet Recurrent (Summary by Unit) in the file V5 Spain.XLS

I have also noticed that without my intervention, the function changed
from:
='[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
to:
='C:\Documents and Settings\username\My Documents\YetAnotherDirectory\
[V5 Spain.XLS]Recurrent (Summary by unit)'!$D$11
so that's the complete path to the file.



The user of this document desires that he gets the data from the other
sheets, without needing to open the sheet. As proven above, this is
not possible with functions. In theory it could be possible with VBA
to construct the hardcoded filenames, but that would be cumbersome
(and slow: hundreds of cells to fill) and it would break the moment
that the files are moved to another directory. I *can* do that, and
have done so in the past, but I'd hate to pass this on to the next
person after me who has to maintain it.

Please advise.

 

[Pete_UK]

INDIRECT does not work with closed workbooks.

However, if you are allowed to use add-ins, then there is a function
INDIRECT.EXT in the free download morefunc, which does allow you to do
this. I understand that you can embed the necessary code into your
workbook and distribute it, so that your users do not have to have
morefunc installed. A Google search on morefunc will reveal a number
of places where you can get the download.

Hope this helps.

Pete

I inherited a behemoth of an Excel file (Excel 2003) that was created
by multiple people in different countries, who may or may not have
left the company. Anyway, I'm on my own.

In one of the sheets, I find the following functions:

Cell U1:
='Site General'!U1

Cell U2:
="["&INDIRECT("u$1")&"]Recurrent (Summary by Unit)"&""

Cell U4 and below:
=IF(ISERROR(INDIRECT(ADDRESS($L4;4;;1;U$2)));0;INDIRECT(ADDRESS
($L4;4;;1;U$2)))
($L4 becomes $L5, $L6,...)

The result of these functions is:
Cell U1:
V5 Spain.XLS
--> this is a file that is (or should be) always in the same directory
as the current file.

Cell U2:
[V5 Spain.XLS]Recurrent (Summary by Unit)

Cell U4 when V5 Spain.XLS is not open:
0
Cell U4 when V5 Spain.XLS is also open:
some value from the tab "Recurrent (Summary by Unit)" in the file V5
Spain.XLS

Now I close Excel and reopen the first file, only that file. I do not
open V5 Spain.XLS.

In W11, a blank cell, I put the following function:
=ADDRESS($L11;4;;1;U$2)
The result of that function is the following text string:
'[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11

In X11 I put the following function:
=INDIRECT(W11)
The result is #REF!

Then I copy W11 and I Paste Special -> Values into W12.
The value of W12 is now:
'[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
=INDIRECT(W12) is also #REF!

Then I click with the mouse inside W12 and I change it from:
'[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
to:
='[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
Now the result isn't #REF! but 123456.7: the value of cell D11 on
sheet Recurrent (Summary by Unit) in the file V5 Spain.XLS

I have also noticed that without my intervention, the function changed
from:
='[V5 Spain.XLS]Recurrent (Summary by Unit)'!$D$11
to:
='C:\Documents and Settings\username\My Documents\YetAnotherDirectory\
[V5 Spain.XLS]Recurrent (Summary by unit)'!$D$11
so that's the complete path to the file.

The user of this document desires that he gets the data from the other
sheets, without needing to open the sheet. As proven above, this is
not possible with functions. In theory it could be possible with VBA
to construct the hardcoded filenames, but that would be cumbersome
(and slow: hundreds of cells to fill) and it would break the moment
that the files are moved to another directory. I *can* do that, and
have done so in the past, but I'd hate to pass this on to the next
person after me who has to maintain it.

Please advise.

 

[Amedee Van Gasse]

INDIRECT does not work with closed workbooks.

However, if you are allowed to use add-ins, then there is a function
INDIRECT.EXT in the free download morefunc, which does allow you to do
this. I understand that you can embed the necessary code into your
workbook and distribute it, so that your users do not have to have
morefunc installed. A Google search on morefunc will reveal a number
of places where you can get the download.

I added the following piece of code to the sheet:

Public Sub UpdateRow1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim c As Range
For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows
(1))
If LCase(Right(c.Value, 4)) = ".xls" Then
UpdateSiteGeneral c
End If
Next
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub UpdateSiteGeneral(ByVal Target As Range)
Dim p As String
p = ActiveWorkbook.Path & "\" & Target.Value
On Error Resume Next
Workbooks.Open p
On Error GoTo 0
ActiveWindow.WindowState = xlMinimized
End Sub


I know it's a stupid way of working but it works for me and it's not a
lot of code to maintain.
The user now only has to add a button that executes UpdateRow1.