Harlan Grove PULL Code Help Please
deeds
anyone know what I need to do with this code? I pasted it in the code area
of the sheet I am working on...but now what? What modifications do I need to
do? Any help would be great!
Thanks in advance.
Harlan Grove
It's a udf, so you need to put the code into a general module, NOT a
worksheet's class module. If you've never used udfs, read through the
following page.
http://www.mvps.org/dmcritchie/excel/install.htm
Once you have the code in a general module, you should be able to use
it in worksheet formulas. No modifications are needed.
deeds
to read up...however, I still can't seem to get it to work. Do I insert
function (Pull)? What do I need to do to have my Indirect formula work when
the file is closed? Any help would be great! Thanks again!
Harlan Grove
>Thanks Harlan....I did what you said....I also went to the link you
>provided to read up...however, I still can't seem to get it to work.
>Do I insert function (Pull)? What do I need to do to have my
>Indirect formula work when the file is closed? Any help would be
>great! Thanks again!
You'd use pull INSTEAD OF INDIRECT.
If I
- create a new workbook in Excel,
- press [Alt]+[F11] to display the VB Editor (VBE),
- run the VBE menu command Insert > Module to create a GENERAL module
in the workbook,
- paste my latest pull code into that module,
- press [Alt]+[F11] again to return to Excel,
- and enter a formula like
=pull("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")
it returns the same value as the static external reference
='D:\test\[foo.xls]Sheet1'!C5
If you don't get similar results, provide DETAILS on what steps you're
taking to put the pull code into your workbook.
FYI, there's also Laurent Longre's MOREFUNC.XLL add-in, available from
http://xcell05.free.fr/english/, which provides an add-in function
named INDIRECT.EXT which provides pretty much the same functionality
as pull. It may be easier to get MOREFUNC to work.
deeds
this somehow in conjunction with a vlookup. I need to do a vlookup and have
it return the data when file is closed. Any ideas? Thanks.
Harlan Grove
>Thanks again...worded great! However, what I ultimately like to do
>is use this somehow in conjunction with a vlookup. I need to do a
>vlookup and have it return the data when file is closed. Any ideas?
First, use the latest version of the pull code, which is at
ftp://members.aol.com/hrlngrv/pull.zip
With that, if I define the following names,
mfpn d:\foo\[deleteme.xls]
wsn A
rn $B$2:$C$13
and the closed file 'd:\foo\[deleteme.xls]A'!$B$2:$C$13 contains
a 1
b 20
c 300
d 4,000
e 50,000
f 600,000
g 7,000,000
h 80,000,000
i 900,000,000
j 10,000,000,000
k 200,000,000,000
l 3,000,000,000,000
then if in another workbook A1 contains c and B1 contains the formula
=VLOOKUP(A1,pull("'"&mfpn&wsn&"'!"&rn),2)
the formula returns 300, as expected.
You use pull like INDIRECT. Once you've entered its code into a
general module, you can just start using it in worksheet formulas.
deeds
Attribute VB_Name = "pull"
Option Explicit
It is stopping on this and bringing me to the code....any idea what I am
doing wrong? Thanks again!
"Harlan Grove" wrote:
> deeds <d...@discussions.microsoft.com> wrote...
> >Thanks again...worded great! However, what I ultimately like to do
> >is use this somehow in conjunction with a vlookup. I need to do a
> >vlookup and have it return the data when file is closed. Any ideas?
> ....
Harlan Grove
> . . . Now I am getting hung up on the first 2 lines of code...
>Attribute VB_Name = "pull"
>Option Explicit
Delete the Attribute line. It's generated when exporting from the VBE,
and when imported into the VBE it'd name the new module. But you're
pasting code, so it serves no purpose other than to throw syntax
errors.
deeds
giving me a "The formula contains an error..."message. I setup your exact
example...here is my formula:
=VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)
What is the problem with this?? Thanks again.
"Harlan Grove" wrote:
> deeds <d...@discussions.microsoft.com> wrote...
> > . . . Now I am getting hung up on the first 2 lines of code...
> >Attribute VB_Name = "pull"
> >Option Explicit
> ....
deeds
result is "#Value!".....any ideas why? Thanks.
Harlan Grove
>Sorry for the troubles....now I can't seem to get the formula to
>work...it is giving me a "The formula contains an error..."
>message. I setup your exact example...here is my formula:
>
>=VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)
>
>What is the problem with this?? Thanks again.
You need to use double quotes around "c:\foo\[deleteme.xls]", "A" and
"$B$2:$C$8", so the formula needs to be
=VLOOKUP(A1,pull("'"&"c:\foo\[deleteme.xls]"&"A"&"'!"&"$B$2:$C$8"),2)
In your formula above, the c:\foo\[deleteme.xls] would be a syntax
error, A would have been interpreted as a defined names in the calling
workbook (the workbook containing the formula containing the pull
call), and $B$2:$C$8 would have been evaluated as an array of the
values in the same worksheet in the calling workbook.
pull's one & only argument is a STRING. It may be easier to construct
its string argument in a different cell with a leading equal sign,
e.g.,
X99:
="='"&"c:\foo\[deleteme.xls]"&"A"&"'!"&"$B$2:$C$8"
which should evaluate to
='c:\foo\[deleteme.xls]A'!$B$2:$C$8
Copy that cell and paste special as value into another cell, then in
that other cell press [F2] then [Enter] or for multiple cell ranges
[Shift]+[Ctrl]+[Enter]. That'll enter the string as a formula. If THAT
doesn't evaluate correctly, there's a problem in your reference that
has nothing to do with pull.
If this constructed literal external reference works, then use it in
the pull call, e.g.,
=VLOOKUP(A1,pull(MID(X99,2,255)),2)
where the MID call skips over the leading = in the X99 formula. In
other words, if you get the pasted-as-value result of the X99 formula
to evaluate correctly as a literal external reference, pull should
evaluate correctly using MID(X99,2,255) as its argument.
deeds
"Harlan Grove" wrote:
> deeds <d...@discussions.microsoft.com> wrote...
> >Sorry for the troubles....now I can't seem to get the formula to
> >work...it is giving me a "The formula contains an error..."
> >message. I setup your exact example...here is my formula:
> >
> >=VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)
> >
> >What is the problem with this?? Thanks again.
> ....
deeds
one with Sumproduct....will these work with the pull method too? I am trying
the countif formula...but so far coming up with #Value! Any thoughts?
Thanks!
"Harlan Grove" wrote:
> deeds <d...@discussions.microsoft.com> wrote...
> >Sorry for the troubles....now I can't seem to get the formula to
> >work...it is giving me a "The formula contains an error..."
> >message. I setup your exact example...here is my formula:
> >
> >=VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)
> >
> >What is the problem with this?? Thanks again.
> ....
Harlan Grove
>I knew I would be back....now...I have other formulas: one with
>Countif and one with Sumproduct....will these work with the pull
>method too? I am trying the countif formula...but so far coming
>up with #Value! Any thoughts?
SUMPRODUCT should work, but COUNTIF won't. COUNTIF (and SUMIF) *only*
accept range references as their 1st (and SUMIF's optional 3rd)
arguments, and Excel only evaluates references to ranges in OPEN
workbooks as range references. It evaluates references to ranges in
closed workbooks are arrays. pull does no differently.
You need to change your COUNTIF formulas to equivalent SUMPRODUCT
formulas.
deeds
formula using pull so I can see how it should work...if you can use your
previous example that would be great...thanks.
"Harlan Grove" wrote:
> deeds <d...@discussions.microsoft.com> wrote...
> >I knew I would be back....now...I have other formulas: one with
> >Countif and one with Sumproduct....will these work with the pull
> >method too? I am trying the countif formula...but so far coming
> >up with #Value! Any thoughts?
> ....
Harlan Grove
>Thanks...if you wouldn't mind, could you throw me a standard
>sumproduct formula using pull so I can see how it should work...if
>you can use your previous example that would be great...thanks.
With c:\foo\deleteme.xls open, the following works.
=COUNTIF([deleteme.xls]A!$C$2:$C$13,"<1E6") returns 6
But with c:\foo\deleteme.xls closed, this formula fails and becomes
=COUNTIF('C:\foo\[deleteme.xls]A'!$C$2:$C$13,"<1E6") returns #VALUE!
The equivalent SUMPRODUCT formula
=SUMPRODUCT(--('C:\foo\[deleteme.xls]A'!$C$2:$C$13<1E6))
returns 6 whether c:\foo\deleteme.xls is open or closed. Replace the
literal external reference with an equivalent pull call.
=SUMPRODUCT(--(pull("'"&"C:\foo\"&"["&"deleteme.xls"&"]"&"A"&"'!"
&"$C$2:$C$13")<1E6))
deeds
need to change to have the [filename] only in another cell...which is
referenced in the pull formula. In otherwords....I want the pull formula to
go look to another cell for the filename (of the closed workbook)...so
something like +Sheet1!A1 which in this cell is the filename (only) not the
path and sheet name etc...
Thanks much!
Harlan Grove
>Thanks again!....One more minor thing....in these formulas...what
>would I need to change to have the [filename] only in another
>cell...which is referenced in the pull formula. In otherwords....I
>want the pull formula to go look to another cell for the filename
>(of the closed workbook)...so something like +Sheet1!A1 which in
>this cell is the filename (only) not the path and sheet name etc...
Sorry, this you need to figure out on your own. It's simple string
concatenation: pull's one & only argument is a string value. If you
can't figure out on your own how to construct strings from various
text values in different cells or defined names, you REALLY shouldn't
be trying to use pull (or any of its equivalents).
deeds
"Harlan Grove" wrote:
> deeds <d...@discussions.microsoft.com> wrote...
> >Thanks again!....One more minor thing....in these formulas...what
> >would I need to change to have the [filename] only in another
> >cell...which is referenced in the pull formula. In otherwords....I
> >want the pull formula to go look to another cell for the filename
> >(of the closed workbook)...so something like +Sheet1!A1 which in
> >this cell is the filename (only) not the path and sheet name etc...
> ....
pat henriquez
I am having some problem using your code. Your resonses above were very
helpful. Thanks for posting it by the way.
I am trying to 'pull' a range, say {1,2,3}. When the target workbook is open
I can retrieve the entire range. However, when the target workbook is closed
I can only retrieve the first element in the range, 1 in this example.
Thanks in advance.
Pat
Flemming Christensen
I am strugling to understand what Harlon Grove's PULL function offers over and above what Excel 2003 already offers when you open a workbook containing links to other data sources and then press update.
Regards
Flemming
> On Tuesday, March 13, 2007 9:19 AM deed wrote:
> I have found the PULL code for updating links in closed files. However, does
> anyone know what I need to do with this code? I pasted it in the code area
> of the sheet I am working on...but now what? What modifications do I need to
> do? Any help would be great!
>
> Thanks in advance.
>> On Tuesday, March 13, 2007 12:57 PM Harlan Grove wrote:
>> deeds <d...@discussions.microsoft.com> wrote...
>>
>> It's a udf, so you need to put the code into a general module, NOT a
>> worksheet's class module. If you've never used udfs, read through the
>> following page.
>>
>> http://www.mvps.org/dmcritchie/excel/install.htm
>>
>> Once you have the code in a general module, you should be able to use
>> it in worksheet formulas. No modifications are needed.
>>> On Wednesday, March 14, 2007 8:47 AM deed wrote:
>>> Thanks Harlan....I did what you said....I also went to the link you provided
>>> to read up...however, I still can't seem to get it to work. Do I insert
>>> function (Pull)? What do I need to do to have my Indirect formula work when
>>> the file is closed? Any help would be great! Thanks again!
>>>
>>> "Harlan Grove" wrote:
>>>> On Wednesday, March 14, 2007 12:01 PM Harlan Grove wrote:
>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>
>>>> You'd use pull INSTEAD OF INDIRECT.
>>>>
>>>> If I
>>>>
>>>> - create a new workbook in Excel,
>>>> - press [Alt]+[F11] to display the VB Editor (VBE),
>>>> - run the VBE menu command Insert > Module to create a GENERAL module
>>>> in the workbook,
>>>> - paste my latest pull code into that module,
>>>> - press [Alt]+[F11] again to return to Excel,
>>>> - and enter a formula like
>>>>
>>>> =pull("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")
>>>>
>>>> it returns the same value as the static external reference
>>>>
>>>> ='D:\test\[foo.xls]Sheet1'!C5
>>>>
>>>> If you don't get similar results, provide DETAILS on what steps you're
>>>> taking to put the pull code into your workbook.
>>>>
>>>> FYI, there's also Laurent Longre's MOREFUNC.XLL add-in, available from
>>>> http://xcell05.free.fr/english/, which provides an add-in function
>>>> named INDIRECT.EXT which provides pretty much the same functionality
>>>> as pull. It may be easier to get MOREFUNC to work.
>>>>> On Wednesday, March 14, 2007 12:52 PM deed wrote:
>>>>> Thanks again...worded great! However, what I ultimately like to do is use
>>>>> this somehow in conjunction with a vlookup. I need to do a vlookup and have
>>>>> it return the data when file is closed. Any ideas? Thanks.
>>>>>
>>>>> "Harlan Grove" wrote:
>>>>>> On Wednesday, March 14, 2007 1:15 PM Harlan Grove wrote:
>>>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>>>> On Wednesday, March 14, 2007 1:50 PM deed wrote:
>>>>>>> Very nice! Now I am getting hung up on the first 2 lines of code...
>>>>>>> Attribute VB_Name = "pull"
>>>>>>> Option Explicit
>>>>>>>
>>>>>>> It is stopping on this and bringing me to the code....any idea what I am
>>>>>>> doing wrong? Thanks again!
>>>>>>>
>>>>>>> "Harlan Grove" wrote:
>>>>>>>> On Wednesday, March 14, 2007 4:18 PM Harlan Grove wrote:
>>>>>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>>>>> ...
>>>>>>>>
>>>>>>>> Delete the Attribute line. It's generated when exporting from the VBE,
>>>>>>>> and when imported into the VBE it'd name the new module. But you're
>>>>>>>> pasting code, so it serves no purpose other than to throw syntax
>>>>>>>> errors.
>>>>>>>>> On Thursday, March 15, 2007 8:41 AM deed wrote:
>>>>>>>>> Sorry for the troubles....now I can't seem to get the formula to work...it is
>>>>>>>>> giving me a "The formula contains an error..."message. I setup your exact
>>>>>>>>> example...here is my formula:
>>>>>>>>> =VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)
>>>>>>>>>
>>>>>>>>> What is the problem with this?? Thanks again.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "Harlan Grove" wrote:
>>>>>>>>>> On Thursday, March 15, 2007 8:49 AM deed wrote:
>>>>>>>>>> Ok....now I got passed the formula error (missing single quotes)...now the
>>>>>>>>>> result is "#Value!".....any ideas why? Thanks.
>>>>>>>>>>
>>>>>>>>>> "deeds" wrote:
>>>>>>>>>>> On Thursday, March 15, 2007 10:58 AM Harlan Grove wrote:
>>>>>>>>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>>>>>>>>> On Thursday, March 15, 2007 12:49 PM deed wrote:
>>>>>>>>>>>> It worked!....Truly outstanding!...Nice work! Thanks for everything!
>>>>>>>>>>>>
>>>>>>>>>>>> "Harlan Grove" wrote:
>>>>>>>>>>>>> On Thursday, March 15, 2007 1:01 PM deed wrote:
>>>>>>>>>>>>> I knew I would be back....now...I have other formulas: one with Countif and
>>>>>>>>>>>>> one with Sumproduct....will these work with the pull method too? I am trying
>>>>>>>>>>>>> the countif formula...but so far coming up with #Value! Any thoughts?
>>>>>>>>>>>>> Thanks!
>>>>>>>>>>>>>
>>>>>>>>>>>>> "Harlan Grove" wrote:
>>>>>>>>>>>>>> On Thursday, March 15, 2007 1:35 PM Harlan Grove wrote:
>>>>>>>>>>>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>>>>>>>>>>> ...
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> SUMPRODUCT should work, but COUNTIF won't. COUNTIF (and SUMIF) *only*
>>>>>>>>>>>>>> accept range references as their 1st (and SUMIF's optional 3rd)
>>>>>>>>>>>>>> arguments, and Excel only evaluates references to ranges in OPEN
>>>>>>>>>>>>>> workbooks as range references. It evaluates references to ranges in
>>>>>>>>>>>>>> closed workbooks are arrays. pull does no differently.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> You need to change your COUNTIF formulas to equivalent SUMPRODUCT
>>>>>>>>>>>>>> formulas.
>>>>>>>>>>>>>>> On Thursday, March 15, 2007 2:08 PM deed wrote:
>>>>>>>>>>>>>>> Thanks...if you wouldn't mind, could you throw me a standard sumproduct
>>>>>>>>>>>>>>> formula using pull so I can see how it should work...if you can use your
>>>>>>>>>>>>>>> previous example that would be great...thanks.
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> "Harlan Grove" wrote:
>>>>>>>>>>>>>>>> On Thursday, March 15, 2007 2:26 PM Harlan Grove wrote:
>>>>>>>>>>>>>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> With c:\foo\deleteme.xls open, the following works.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> =COUNTIF([deleteme.xls]A!$C$2:$C$13,"<1E6") returns 6
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> But with c:\foo\deleteme.xls closed, this formula fails and becomes
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> =COUNTIF('C:\foo\[deleteme.xls]A'!$C$2:$C$13,"<1E6") returns #VALUE!
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> The equivalent SUMPRODUCT formula
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> =SUMPRODUCT(--('C:\foo\[deleteme.xls]A'!$C$2:$C$13<1E6))
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> returns 6 whether c:\foo\deleteme.xls is open or closed. Replace the
>>>>>>>>>>>>>>>> literal external reference with an equivalent pull call.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> =SUMPRODUCT(--(pull("'"&"C:\foo\"&"["&"deleteme.xls"&"]"&"A"&"'!"
>>>>>>>>>>>>>>>> &"$C$2:$C$13")<1E6))
>>>>>>>>>>>>>>>>> On Thursday, March 15, 2007 2:46 PM deed wrote:
>>>>>>>>>>>>>>>>> Thanks again!....One more minor thing....in these formulas...what would I
>>>>>>>>>>>>>>>>> need to change to have the [filename] only in another cell...which is
>>>>>>>>>>>>>>>>> referenced in the pull formula. In otherwords....I want the pull formula to
>>>>>>>>>>>>>>>>> go look to another cell for the filename (of the closed workbook)...so
>>>>>>>>>>>>>>>>> something like +Sheet1!A1 which in this cell is the filename (only) not the
>>>>>>>>>>>>>>>>> path and sheet name etc...
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Thanks much!
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> "Harlan Grove" wrote:
>>>>>>>>>>>>>>>>>> On Thursday, March 15, 2007 4:19 PM Harlan Grove wrote:
>>>>>>>>>>>>>>>>>> deeds <d...@discussions.microsoft.com> wrote...
>>>>>>>>>>>>>>>>>> ...
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Sorry, this you need to figure out on your own. It's simple string
>>>>>>>>>>>>>>>>>> concatenation: pull's one & only argument is a string value. If you
>>>>>>>>>>>>>>>>>> can't figure out on your own how to construct strings from various
>>>>>>>>>>>>>>>>>> text values in different cells or defined names, you REALLY shouldn't
>>>>>>>>>>>>>>>>>> be trying to use pull (or any of its equivalents).
>>>>>>>>>>>>>>>>>>> On Friday, March 16, 2007 8:44 AM deed wrote:
>>>>>>>>>>>>>>>>>>> Will do....thanks again for all your help!
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> "Harlan Grove" wrote:
>>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>>>>>>>>> Microsoft SQL Server Developer For Beginners
>>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/training-topic-area/SQL-Server-Developer/5/SQL-Server.aspx
Pete_UK
workbook which is closed then it will not work and will return an
error. It will work if the external workbook is open.
However, it is not always convenient to have that external workbook
open, as it will take up memory and may slow other processes down, and
besides there might be several external workbooks that the data is
needed from. The PULL function can be used in this case. As Harlan
mentions in one of his replies, there is a free add-in called morefunc
which has a similar function INDIRECT.EXT, which can be used in a
similar way.
Hope this helps.
Pete
On Dec 31, 1:28 am, Flemming Christensen <f...@beaconplus.co.uk>
wrote:
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -