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?

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 -----

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!
>
>