Referencing another worksheet

[Guest]

I am using a text string to reference a block of cells in another worksheet.
The text string is in a seperate cell, and I am using this string in a
multitude of other cells using INDIRECT(TextStringCell). If the text string
is in the form
[Workbook]Worksheet!A1:B2, for example, i have to have the referenced
workbook open for the link to work. I thought perhaps that if the string was
in the form
C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced
workbook open. Needless to say, this doesn't work.

On other spreadsheets, where i do not call a reference indirectly, i can
happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do
not need to have the referenced workbook open. Is this problem just a quirk
of an indirect reference, or is there a way around it? Perhaps I'm missing
some apostrophes some where....

Thanks in advance.

 

[Bob Phillips]

INDIRECT only works with open workbooks.

 

[Arvi Laanemets]

Hi

INDIRECT doesn't work with closed workbooks. you have to use some different
solution.

a) When the number of workbooks you are linking to is limited, then you can
mirror them on some hidden sheet. Now you refer to this/those hidden
sheet(s) instead of files (I have a project where ~20 workbooks are linked
to summary workbook in such a way).
b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or
Add-In on his site to download, which is an equivalent for INDIRECT, but
works with closed workbooks too. But when you want to design a workbook for
use by several users, all of them must have it in their computers - which
can be a problem.
c) Design your workbook to link to some fixed workbook. When you want to
swich the source workbook, you save it with this fixed name (not a good
solution, but for some occassions will do).
d) Create a procedure, which asks for workbook you want to link to, and
rewrites then all links. You can start the procedure from hot key, or from
button placed on worksheet.
e) ... I'm sure there are other possible solutions, but for start it will
do.

 

[Bob Phillips]

b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or
Add-In on his site to download, which is an equivalent for INDIRECT, but
works with closed workbooks too. But when you want to design a workbook for
use by several users, all of them must have it in their computers - which
can be a problem.

That would be Harlan Grove who wrote a UDF called PULL.

You can find the function at his FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

 

[Guest]

That would explain my problems!

Lots of good solutions there. The first suggestioon seems the most fitting
for my purposes, since I only have 2 referenced workbooks. It would be easy
enough just to have those two open, but its a pain when you forget or open
the wrong one accidently. So, rather than mirror a whole load of sheets onto
hidden sheets in my open file, i was wondering how easy it would to open the
two referenced files automatically when I open the master file? (And close
them automatically?) Could you do this with a macro?

Hi

INDIRECT doesn't work with closed workbooks. you have to use some different
solution.

a) When the number of workbooks you are linking to is limited, then you can
mirror them on some hidden sheet. Now you refer to this/those hidden
sheet(s) instead of files (I have a project where ~20 workbooks are linked
to summary workbook in such a way).
b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or
Add-In on his site to download, which is an equivalent for INDIRECT, but
works with closed workbooks too. But when you want to design a workbook for
use by several users, all of them must have it in their computers - which
can be a problem.
c) Design your workbook to link to some fixed workbook. When you want to
swich the source workbook, you save it with this fixed name (not a good
solution, but for some occassions will do).
d) Create a procedure, which asks for workbook you want to link to, and
rewrites then all links. You can start the procedure from hot key, or from
button placed on worksheet.
e) ... I'm sure there are other possible solutions, but for start it will
do.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

I am using a text string to reference a block of cells in another
worksheet.
The text string is in a seperate cell, and I am using this string in a
multitude of other cells using INDIRECT(TextStringCell). If the text
string
is in the form
[Workbook]Worksheet!A1:B2, for example, i have to have the referenced
workbook open for the link to work. I thought perhaps that if the string
was
in the form
C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced
workbook open. Needless to say, this doesn't work.

On other spreadsheets, where i do not call a reference indirectly, i can
happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do
not need to have the referenced workbook open. Is this problem just a
quirk
of an indirect reference, or is there a way around it? Perhaps I'm missing
some apostrophes some where....

Thanks in advance.

 

[Guest]

Will also try the PULL function. Thanks!

That would be Harlan Grove who wrote a UDF called PULL.

You can find the function at his FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

 

[Guest]

My VBA knowledge is very limited (usually to the record/stop button on the
macro toolbar). I have copied the code to a new module in my workbook, and
the pull function appears in my UDF list. As a way of a test, I am just
trying to reference one cell in a closed workbook using a string in A1, and
=pull(a1) in another cell. Howewer, all i get is #value. Have tried with an
open workbook, with and without the file path, with & without apostrophes....
Any suggestions?

I get the feeling I'm over-complicating things now, but i've got this far....

 

[Arvi Laanemets]

Hi

That would explain my problems!

Lots of good solutions there. The first suggestioon seems the most fitting
for my purposes, since I only have 2 referenced workbooks. It would be
easy
enough just to have those two open, but its a pain when you forget or open
the wrong one accidently. So, rather than mirror a whole load of sheets
onto
hidden sheets in my open file, i was wondering how easy it would to open
the
two referenced files automatically when I open the master file? (And close
them automatically?) Could you do this with a macro?

Not with macro, but you can do it p.e. in workbook's Open event:
Open VBA editor (Alt+F11);
In VBA Project window, right-click on ThisWorkbook beneath your project;
Click on 'View Code' in dropdown menu;
In left combo on top of code window, select 'Workbook'. A dummy Open event
is created automatically - fill it with your code.

About mirroring data - you don't need to mirror all sheets and columns -
only those you need to refer to. The easiest way to create a mirror:
Open the workbook, you want to link to. Activate target workbook;
Insert an empty sheet (and name it);
Into cell A1 on created sheet, enter the formula like
=IF('[SourceWorkbook.xls]SourceSheet'!A1="","",'[SourceWorkbook.xls]SourceSheet'!A1)
Copy the formula to range, including all wanted data (+ some amount of empty
rows at bottom, when new data will be added into source workbook later);
Delete all abundant columns (the ones you don't refer to) in mirrored table
(NB! Delete entire columns - otherwise link formulas will be screwed);
Close the source workbook.


--
Arvi Laanemets

Hi

INDIRECT doesn't work with closed workbooks. you have to use some
different
solution.

a) When the number of workbooks you are linking to is limited, then you
can
mirror them on some hidden sheet. Now you refer to this/those hidden
sheet(s) instead of files (I have a project where ~20 workbooks are
linked
to summary workbook in such a way).
b) Some frequent resident of Excel NG's (I don't remember who) has an UDF
or
Add-In on his site to download, which is an equivalent for INDIRECT, but
works with closed workbooks too. But when you want to design a workbook
for
use by several users, all of them must have it in their computers - which
can be a problem.
c) Design your workbook to link to some fixed workbook. When you want to
swich the source workbook, you save it with this fixed name (not a good
solution, but for some occassions will do).
d) Create a procedure, which asks for workbook you want to link to, and
rewrites then all links. You can start the procedure from hot key, or
from
button placed on worksheet.
e) ... I'm sure there are other possible solutions, but for start it will
do.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

I am using a text string to reference a block of cells in another
worksheet.
The text string is in a seperate cell, and I am using this string in a
multitude of other cells using INDIRECT(TextStringCell). If the text
string
is in the form
[Workbook]Worksheet!A1:B2, for example, i have to have the referenced
workbook open for the link to work. I thought perhaps that if the
string
was
in the form
C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced
workbook open. Needless to say, this doesn't work.

On other spreadsheets, where i do not call a reference indirectly, i
can
happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I
do
not need to have the referenced workbook open. Is this problem just a
quirk
of an indirect reference, or is there a way around it? Perhaps I'm
missing
some apostrophes some where....

Thanks in advance.

 

[Guest]

Not proficient at VBA - the extent of my use of macros is the record/play
button on the macro toolbar! Will try the mirrored sheets.

Thanks for your help.

Hi

That would explain my problems!

Lots of good solutions there. The first suggestioon seems the most fitting
for my purposes, since I only have 2 referenced workbooks. It would be
easy
enough just to have those two open, but its a pain when you forget or open
the wrong one accidently. So, rather than mirror a whole load of sheets
onto
hidden sheets in my open file, i was wondering how easy it would to open
the
two referenced files automatically when I open the master file? (And close
them automatically?) Could you do this with a macro?

Not with macro, but you can do it p.e. in workbook's Open event:
Open VBA editor (Alt+F11);
In VBA Project window, right-click on ThisWorkbook beneath your project;
Click on 'View Code' in dropdown menu;
In left combo on top of code window, select 'Workbook'. A dummy Open event
is created automatically - fill it with your code.

About mirroring data - you don't need to mirror all sheets and columns -
only those you need to refer to. The easiest way to create a mirror:
Open the workbook, you want to link to. Activate target workbook;
Insert an empty sheet (and name it);
Into cell A1 on created sheet, enter the formula like
=IF('[SourceWorkbook.xls]SourceSheet'!A1="","",'[SourceWorkbook.xls]SourceSheet'!A1)
Copy the formula to range, including all wanted data (+ some amount of empty
rows at bottom, when new data will be added into source workbook later);
Delete all abundant columns (the ones you don't refer to) in mirrored table
(NB! Delete entire columns - otherwise link formulas will be screwed);
Close the source workbook.


--
Arvi Laanemets

Hi

INDIRECT doesn't work with closed workbooks. you have to use some
different
solution.

a) When the number of workbooks you are linking to is limited, then you
can
mirror them on some hidden sheet. Now you refer to this/those hidden
sheet(s) instead of files (I have a project where ~20 workbooks are
linked
to summary workbook in such a way).
b) Some frequent resident of Excel NG's (I don't remember who) has an UDF
or
Add-In on his site to download, which is an equivalent for INDIRECT, but
works with closed workbooks too. But when you want to design a workbook
for
use by several users, all of them must have it in their computers - which
can be a problem.
c) Design your workbook to link to some fixed workbook. When you want to
swich the source workbook, you save it with this fixed name (not a good
solution, but for some occassions will do).
d) Create a procedure, which asks for workbook you want to link to, and
rewrites then all links. You can start the procedure from hot key, or
from
button placed on worksheet.
e) ... I'm sure there are other possible solutions, but for start it will
do.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


I am using a text string to reference a block of cells in another
worksheet.
The text string is in a seperate cell, and I am using this string in a
multitude of other cells using INDIRECT(TextStringCell). If the text
string
is in the form
[Workbook]Worksheet!A1:B2, for example, i have to have the referenced
workbook open for the link to work. I thought perhaps that if the
string
was
in the form
C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced
workbook open. Needless to say, this doesn't work.

On other spreadsheets, where i do not call a reference indirectly, i
can
happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I
do
not need to have the referenced workbook open. Is this problem just a
quirk
of an indirect reference, or is there a way around it? Perhaps I'm
missing
some apostrophes some where....

Thanks in advance.

 

[Bob Phillips]

I just tried it and I see that the version on Harlan's site is not the
latest version. I attach the latest version here.

You need to be very careful in defining the cell data, along the lines of

'C:\[workbook.xls]sheetname'!cell_ref

be especially careful with the leading ', you will probably need two, '', as
one just tells Excel it is text.

'----- begin VBA -----
Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
'2004-05-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------
'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-30 changes **
'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(xref, "\")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1,
xlR1C1))
Next C
pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
'----- end VBA -----

 

[Guest]

Yep, that code works properly for my single cell refence test, but when I try
to use it for my other formulae it sends excel a bit loopy!

The string i'm referencing with PULL() is a a reference to an array of cells
of size 2Rx200C. The PULL function is itself used in a formula involving IF,
VLOOKUP, INDEX amongst others. It also seems to work if I do find 'INDIRECT'
replace 'PULL' for one cell, but if I do replace all it freezes on me. I
think I'll go ahead with Arvi's mirrored sheets; doubles my file size but i
know it works for my sheet.

Thanks for the code and the advice Bob, PULL() may yet prove to be useful!

I just tried it and I see that the version on Harlan's site is not the
latest version. I attach the latest version here.

You need to be very careful in defining the cell data, along the lines of

'C:\[workbook.xls]sheetname'!cell_ref

be especially careful with the leading ', you will probably need two, '', as
one just tells Excel it is text.

'----- begin VBA -----
Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
'2004-05-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------
'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-30 changes **
'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(xref, "\")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1,
xlR1C1))
Next C
pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
'----- end VBA -----



--
HTH

Bob Phillips

My VBA knowledge is very limited (usually to the record/stop button on the
macro toolbar). I have copied the code to a new module in my workbook, and
the pull function appears in my UDF list. As a way of a test, I am just
trying to reference one cell in a closed workbook using a string in A1, and
=pull(a1) in another cell. Howewer, all i get is #value. Have tried with an
open workbook, with and without the file path, with & without apostrophes....
Any suggestions?

I get the feeling I'm over-complicating things now, but i've got this far....