INDIRECT and Named Ranges referencing closed workbook

I have tried using PULL from Harlan Grove's posts to workaround this
but am coming up with #VALUE errors.

Here's what I have:

Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230

I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R
however it returns #REF!

I need a solution to replace INDIRECT so I do not have to have both
workbooks open together. The named ranges are static, but reference
external workbooks.

TIA!

gpie wrote...
>I have tried using PULL from Harlan Grove's posts to workaround this
>but am coming up with #VALUE errors.
>
>Here's what I have:
>
>Column B contains the acct # being referenced e.g. 5230
>Column E="_"&Br where r is the row #

So this col E cell would evaluate to "_5230" ?

>Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
>range referring to an external workbook e.g. _5230Rows

_5230Rows won't be interpretted as a reference into an external
workbook. You need to include the drive/directory path and filename.

>Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
>range referencing an external workbook e.g. _5230

If _5230Rows refers to the first column of _5230, use one formula
rather than two - eliminate the col F formulas and use

=VLOOKUP($B$3,INDIRECT($Er),COLUMN(G$6))

However, same comment as above about the need for drive/directory path
and filename.

>I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R
>however it returns #REF!

Are you sure you don't mean you thought

INDEX( INDIRECT( $Er ), $Fr, COLUMN(G$6))

should work?

>I need a solution to replace INDIRECT so I do not have to have both
>workbooks open together. The named ranges are static, but reference
>external workbooks.

What did your pull formulas that returned #VALUE! *REALLY* look like?

I've used INDIRECT.EXT with success.

http://xcell05.free.fr/english/moref...direct.ext.htm

You can download it here

http://xcell05.free.fr/english/index...func_Functions


"gpie" wrote:

> I have tried using PULL from Harlan Grove's posts to workaround this
> but am coming up with #VALUE errors.
>
> Here's what I have:
>
> Column B contains the acct # being referenced e.g. 5230
> Column E="_"&Br where r is the row #
> Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
> range referring to an external workbook e.g. _5230Rows
> Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
> range referencing an external workbook e.g. _5230
>
> I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R
> however it returns #REF!
>
> I need a solution to replace INDIRECT so I do not have to have both
> workbooks open together. The named ranges are static, but reference
> external workbooks.
>
> TIA!
>
>

Thanks for your response, Harlan. I think I am misunderstanding how
your PULL function works. I have answered your questions below.
Thanks again for your help!

Harlan Grove wrote:
> gpie wrote...
> >I have tried using PULL from Harlan Grove's posts to workaround this
> >but am coming up with #VALUE errors.
> >
> >Here's what I have:
> >
> >Column B contains the acct # being referenced e.g. 5230
> >Column E="_"&Br where r is the row #
>
> So this col E cell would evaluate to "_5230" ?

Yes.

>
> >Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
> >range referring to an external workbook e.g. _5230Rows
>
> _5230Rows won't be interpretted as a reference into an external
> workbook. You need to include the drive/directory path and filename.
>

In this workbook, _5230Rows is defined as ='[Detail Account
Budgets.xls]5230'!$B$51:$B$70

Detail Account Budget.xls is the external workbook. So I am not
referring to a name in an external workbook, the name refers to the
external workbook. Should I set it up the opposite way?

--snip--

>
> >I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R
> >however it returns #REF!
>
> Are you sure you don't mean you thought
>
> INDEX( INDIRECT( $Er ), $Fr, COLUMN(G$6))
>
> should work?
>

No, that formula does work, as long as I have the other workbook open.
If I hard-code the formula as INDEX(_5230,_5230Rows,COLUMN(G$6)) it
works even with the other book closed, I'm not really sure why $Er
needs the INDIRECT to work. The cell looks like _5230

> >I need a solution to replace INDIRECT so I do not have to have both
> >workbooks open together. The named ranges are static, but reference
> >external workbooks.
>
> What did your pull formulas that returned #VALUE! *REALLY* look like?

Here is one example, using the INDEX function above

=INDEX(PULL($E22),$F22,COLUMN(G$6))

gpie wrote...
>Thanks for your response, Harlan. I think I am misunderstanding how
>your PULL function works. I have answered your questions below.
>Thanks again for your help!
>
>Harlan Grove wrote:
....
[reformatted]
>In this workbook, _5230Rows is defined as
>='[Detail Account Budgets.xls]5230'!$B$51:$B$70

This is the problem with pull. When this other workbook is open, this
defined name resolves to a range reference. When it's closed, this
resolves to an array. pull would choke on either. pull requires a text
argument that looks like a fully qualified external reference - drive,
full directory path, filename, and either worksheet name and range
address or defined name IN THAT OTHER FILE.

>Detail Account Budget.xls is the external workbook. So I am not
>referring to a name in an external workbook, the name refers to the
>external workbook. Should I set it up the opposite way?
....

You can't use pull with this defined name, but you could use pull with
the defined name XRB referring to the string-valued expression

="<your drive/directory path here>\[Detail Account Budgets.xls]"

Note that I'm not including the initial delimiting single quote. Then
you should be able to use pull in

=MATCH($B$3,pull("'"&XRB&B99&&"'!$B$51:$B$70"))

>>>I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R
>>>however it returns #REF!
>>
>>Are you sure you don't mean you thought
>>
>>INDEX( INDIRECT( $Er ), $Fr, COLUMN(G$6))
>>
>>should work?
>
>No, that formula does work, as long as I have the other workbook open.
>If I hard-code the formula as INDEX(_5230,_5230Rows,COLUMN(G$6)) it
>works even with the other book closed, I'm not really sure why $Er
>needs the INDIRECT to work. The cell looks like _5230
....

So $Er does need to be inside INDIRECT? If so, the reason is that Er
evaluates to a string, "_5230", not to a range reference. INDIRECT
converts the string "_5230" into a range reference when the other
workbook is open, in which case it can return a range reference. The
hardcoded formula always works because _5230 (without quotes) is a
range reference rather than a text string.

>Here is one example, using the INDEX function above
>
>=INDEX(PULL($E22),$F22,COLUMN(G$6))

What's E22? If it's just "_5230", pull will choke on it. See my
comments about using defined name XRB above.

Again I really appreciate your help. I have applied your suggestions
and here's where I am:
>
> ="<your drive/directory path here>\[Detail Account Budgets.xls]"
>
> Note that I'm not including the initial delimiting single quote. Then
> you should be able to use pull in
>
> =MATCH($B$3,pull("'"&XRB&B99&&"'!$B$51:$B$70"))
>
The directory will change periodically as I freeze the files and use
new working versions, so I set up the formula as follows:

=MATCH($B$3,PULL("'"&Directory&"\"&DAB&B22&"'!"&DABRows))

Here are the name defs:
Directory ="F:\Budget\2006 Budget\First Draft"
DAB ="[Detail Account Budgets.xls]"
DABRows ="$B51:$B70"

So the formula should evaluate to:
=MATCH($B$3,PULL('F:\Budget\2006 Budget\First Draft\[Detail Account
Budgets.xls]5230'!$B51:$B70))

And it is returning a #VALUE! error.

I tried typing in the text directly without the named ranges and I
still get #VALUE!

If I take the PULL( ) out of the typed version then the formula evalues
as expected.

Am I missing something here about how PULL works?

Thanks again so much for your help.

I think the problem is that I haven't updated the file on my ftp site with
the latest version of pull. Here's the latest version.


'----- 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.
'-----------------------------------------------------------------
'2005-05-02
'fixed InStrRev syntax. Now using XL2K+ syntax.
'-----------------------------------------------------------------
'2005-04-18
'added logic to check for date values from open workbooks, then
'adjust for 1904 date system in source workbooks
'-----------------------------------------------------------------
'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 [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
'-----------------------------------------------------------------
Const DS1904DIFF As Long = 1461

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long, ds1904 As Boolean

'** begin 2004-05-30 changes **
'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
'** 2005-05-02 change - XL2K+ syntax **
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
'** 2005-05-02 change - XL2K+ syntax **
n = InStrRev((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)

'** begin 2005-04-18 changes **
If Not IsError(pull) Then
On Error Resume Next
ds1904 = Workbooks(Right(b, n)).Date1904
Err.Clear
On Error GoTo 0
End If

'** key 2004-05-30 addition **
'** changed in 2005-04-18 changes **
If IsArray(pull) Then
If ds1904 Then
Dim a As Variant, i As Long, j As Long

a = pull
For i = LBound(a, 1) To UBound(a, 1)
For j = LBound(a, 2) To UBound(a, 2)
If VarType(a(i, j)) = vbDate Then _
a(i, j) = a(i, j) + DS1904DIFF
Next j
Next i
pull = a

End If

Exit Function

ElseIf ds1904 And VarType(pull) = vbDate Then
pull = pull + DS1904DIFF

End If
'** end 2004-05-30 changes **
'** end 2005-04-18 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


'** 2005-05-02 change - InStrRev for XL97 using abbreviated XL2K+ syntax
#If Not VBA6 Then
Private Function InStrRev(s As String, ss As String) As Long
Dim k As Long, n As Long

k = Len(ss)
n = Len(s) - k + 1

For n = n To 1 Step -1
If Mid(s, n, k) = ss Then Exit For
Next n

InStrRev = n
End Function
#End If
'----- end VBA -----

Thanks so much!
Works great!!!

I am having trouble getting the PULL formula to update - I have to go
into the cell and click on it, and then Excel "thinks" for about 30
seconds.

Is this usual? Is there a way to fix it?

thanks again so much!

gpie wrote...
>I am having trouble getting the PULL formula to update - I have to go
>into the cell and click on it, and then Excel "thinks" for about 30
>seconds.
>
>Is this usual? Is there a way to fix it?

It's nonvolatile on purpose, meaning it doesn't recalc except on a full
recalc, [Ctrl]+[Alt]+[F9]. If you want the formulas that call pull to
act as if they're volatile, if they're supposed to return numbers add
0*NOW() to them. The volatile NOW call forces the entire formula to
recalc on each minimal recalc (what you get by pressing [F9]). If the
formula is supposed to return a string, append &LEFT(NOW(),0) to it.

BE WARNED, however, that recalculation will become VERY SLOW if every
pull call becomes volatile. It takes significant time to read data from
closed files, much more than it takes when the other files are open.
This is the main reason I made pull nonvolatile. If you can live with
full recalcs using [Ctrl]+[Alt]+[F9], that's the better way to go.