INDIRECT with closed file
Bernard
could anyone assist me with the INDIRECT function?
It appears that, for this function to work correctly, the
external worksheet to which the INDIRECT points MUST be
open when getting the data. But, as I'm dealing with 200
different files, this is practically impossible...
Would anyone have a solution?
Thanks,
Bernard.
Arvi Laanemets
1. When the ranges in source tables aren't big, and are fixed or don't vary
much, you can use an additional (hidden) sheet, to get summary table with
links. And then you can use tis sheet as source for INDIRECT. The data in
summary table are refreshed when worksheet is opened, or manually.
Arvi Laanemets
"Bernard" <theb...@skynet.be> wrote in message
news:01ca01c2a0f8$eb6789c0$89f82ecf@TK2MSFTNGXA01...
Bernard
I'm not quite sure I understand the solution you have
given me... (I'm quite new with Excel :-)))
How do I make that summary table and in what file should I
put it (in the file with the Indirect function OR in the
others containing the original data)?
The source tables are indeed small and don't vary at all...
thanks for your voluntary help !!!
B.
>.
>
Arvi Laanemets
The summary sheet will be in same worksbook where you want to use INDIRECT.
Activate an empty worksheet, or insert one.
Open the first source file, select the whole table (with header row, when
there is one), and copy it.
Select the result workbook (from Windows menu - active workbooks are
displayed at bottom).
Right-click on cell B1 (when there was a header row) or B2 (when there
wasn't any header row, but you want one) on summary sheet. Select
PasteSpecial from dropdown menu, check 'Links', and press OK. Your table
from first source file is displayed on sheet.
For every linked row, enter into column A some value, indicating the table,
the data are taken from where (workbook name, or person name, or ... ).
Open the second source file, select the whole table, except the header row,
and copy it.
Select the result workbook, right-click on cell in column B just below
previously linked table, and link the second table.
Fill the column A for second link.
Continue until all source tables are linked.
There is a modification for case, when not all rows in source files are
filled. The link to empty cell returns 0. To avoid this, replace leftmost
upper link for every table with formula
=IF(YourLink="","",YourLink), and copy the formula (PasteSpecial.Formulas)
over the link range for this table.
Arvi Laanemets
"Bernard" <theb...@skynet.be> wrote in message
news:02e601c2a10d$27146510$89f82ecf@TK2MSFTNGXA01...
Harlan Grove
>could anyone assist me with the INDIRECT function?
>It appears that, for this function to work correctly, the
>external worksheet to which the INDIRECT points MUST be
>open when getting the data. But, as I'm dealing with 200
>different files, this is practically impossible...
There are ways to do this by using VBA. It's safer to use
a macro to create hard-coded links from path, file,
worksheet, and range address components. For example,
create a table like the following
D:\work foo.xls Sheet1 C1
D:\work bar.xls Sheet2 X99
with the column to the right of the table initially blank
so it could accomodate the hard-coded links.
Select the entire table and run a macro like this.
Sub mkextlnk()
Dim r As Range, rng As Range
If Not TypeOf Selection Is Range Then
Msgbox "macro only works when you select a range"
Exit Sub
End If
Set rng = Selection.Areas(1)
Set rng = rng.Resize(rng.Rows.Count, 5)
For Each r In rng.Rows
r.Cells(1, 5).Formula = "='" & r.Cells(1, 1).Value & _
"\[" & r.Cells(1, 2).Value & "]" & _
r.Cells(1, 3).Value & "'!" & r.Cells(1, 4).Value
Next r
End Sub
This approach is far & away the most time-efficient way to
deal with lots of *CLOSED* workbooks.