Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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)) |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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&"'!"&DA BRows)) 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. |
#7
|
|||
|
|||
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 ----- |
#8
|
|||
|
|||
Thanks so much!
Works great!!! |
#9
|
|||
|
|||
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! |
#10
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect Function Doesn't like non-contiguous ranges | Excel Discussion (Misc queries) | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Indirect and dynamic ranges | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
vlookup and named ranges | Excel Worksheet Functions |