Harlan Grove PULL Code Help Please

[Guest]

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.

 

[Harlan Grove]

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!

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.

 

[Guest]

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]

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.

 

[Guest]

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.

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.

 

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

 

[Guest]

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!

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.

 

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

 

[Guest]

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.

 

[Guest]

Ok....now I got passed the formula error (missing single quotes)...now the
result is "#Value!".....any ideas why? Thanks.

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.

....

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.

 

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

 

[Guest]

It worked!....Truly outstanding!...Nice work! Thanks for everything!

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.

 

[Guest]

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!

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.

 

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

 

[Guest]

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]

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

 

[Guest]

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!

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

 

[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).

 

[Guest]

Will do....thanks again for all your help!

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

 

[pat henriquez]

Hi Harlan

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