PULL FUNCTION

[Sandyl]

I wish to use the PULL function as part of a formula but am having
problems doing this. Also notice that the PULL function sometimes does
not work. Am using Excel 2000. The following will occassionally work
but at other times return an error #VALUE!

=pull("'"&B4&"["&B1&".xls]"&B3&"'!K70")

If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")

Am I missing something or using the function incorrectly?

By the way B4=path, B1=workbook name, B3 and I1 = sheet name.

I need this to work with both open and closed workbooks but not sure if
the function actually allows me to use with formulas. I am assuming
also that I have the latest code from Harlan. Can anyone help?

 

[Kassie]

Are you busy with Excel, or with SQL Server

 

[Dave Peterson]

Harlan has tweaked his code a few times.

I think that this is the latest version:
http://groups.google.co.uk/[email protected]

Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You could check there, too.

I wish to use the PULL function as part of a formula but am having
problems doing this. Also notice that the PULL function sometimes does
not work. Am using Excel 2000. The following will occassionally work
but at other times return an error #VALUE!

=pull("'"&B4&"["&B1&".xls]"&B3&"'!K70")

If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")

Am I missing something or using the function incorrectly?

By the way B4=path, B1=workbook name, B3 and I1 = sheet name.

I need this to work with both open and closed workbooks but not sure if
the function actually allows me to use with formulas. I am assuming
also that I have the latest code from Harlan. Can anyone help?

 

[Sandyl]

Dave,

Thanks for the link - now getting data from the pull function but still
getting the error when using within the formula above. Am I missing
the obvious (more than likely!)?

 

[Sandyl]

Kassie,

Am using Excel, hence the question.

 

[Dave Peterson]

I guess your formula looks a bit strange to me.

Did you really mean to check G6, then use K70 in your formula?

If you build the formula manually (not using =indirect()), what's returned?

Maybe you have your folder/file/sheet references in the wrong spot????

 

[Harlan Grove]

but at other times return an error #VALUE! ....
If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),
pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")

....

Basic formula debugging necessary.

You're changing worksheet name and cell address. Step 1 is entering the
formulas

="='"&B4&"["&B1&".xls]"&I1&"'!g6"

and

="='"&B4&"["&B1&".xls]"&B3&"'!K70"

Then copy them and paste special as values in other cells, then Edit >
Replace = with = in those cells. What do the literal external references
return?

 

[Sandyl]

First part of formula is checking for true or false on sheet1 (G6) and
if true entering value of cell K70 from sheet2 - in this case it is a
date but am using formulas for various purposes so only wanted to know
if PULL can be used in this way as I don't want to continually type in
the filename of each spreadsheet (this will continually grow).

I should point out that all my formulas work correctly if using
complete file name and path so no problem there. Just trying to
understand how to use the PULL function and see if this solves my
problems.

 

[Harlan Grove]

Sandyl wrote...
....

. . . so only wanted to know
if PULL can be used in this way as I don't want to continually type in
the filename of each spreadsheet (this will continually grow).

....

pull can be used the same way as any built-in function can be used, so

=IF(pull(OneThing),pull(SomethingElse),"")

should work.

 

[Sandyl]

Harlan,

Many thanks for the confirmation. Tried this but got a #value error as
I initially stated. Will try again and ensure that it isn't a simple
typo!

 

[Harlan Grove]

Sandyl wrote...

Many thanks for the confirmation. Tried this but got a #value error as
I initially stated. Will try again and ensure that it isn't a simple
typo!

So, are you going to respond to the debugging questions I asked two
replies ago? The pull functions works for me. If it doesn't for you,
the odds are the error(s) is(are) on your end.

 

[Sandy]

Sandyl wrote...

So, are you going to respond to the debugging questions I asked two
replies ago? The pull functions works for me. If it doesn't for you,
the odds are the error(s) is(are) on your end.

Harlan,

Sorry, thought I had. I did check formulas with full path and
filename then tried PULL but got same result. As I stated previously,
I will now look more closely at my typing to ensure that syntax is
correct.

Many thanks for your generous help to date.

 

[Sandy]

Harlan,

Many thanks for the confirmation. Tried this but got a #value error as
I initially stated. Will try again and ensure that it isn't a simple
typo!

Harlan,

Sorry didn't specifically answer. Yes I debugged and got the result
expected. Still having problems but will persevere and advise on the
outcome shortly. Day job gets in the way!

Thanks,

Sandy

 

[Sandy]

Sandyl wrote...

So, are you going to respond to the debugging questions I asked two
replies ago? The pull functions works for me. If it doesn't for you,
the odds are the error(s) is(are) on your end.

Harlan,

Just so that there is no misunderstanding, I have de-bugged and the
filename and path are correct. Now trying to figure out why it won't
work with my formula. As you say, it is more than likely a typo on my
part. Will keep you informed.

Thanks for everything - I really appreciate it.

 

[Sandy]

Harlan,

Sorry didn't specifically answer. Yes I debugged and got the result
expected. Still having problems but will persevere and advise on the
outcome shortly. Day job gets in the way!

Thanks,

Sandy

Harlan,

Have now made progress. Unfortunately, this has highlighted a couple
of problems:

1. If I pull a date from an open workbook, it responds with the
wrong base (i.e. 15 Apr 05 returns 16 Apr 09). Close the workbook and
I then get the right date.
2. Can't actually auto update with Pull. Should it? Bit of a major
problem if it does not.
3. When copying and pasting to multiple cells, I must then enter
each cell formula and physicallly press the Enter key to update the
cell contents.

Would appreciate your further assistance and clarification.

Regards,

Sandy

 

[Harlan Grove]

Sandy wrote...
....

Have now made progress. Unfortunately, this has highlighted a couple
of problems:

1. If I pull a date from an open workbook, it responds with the
wrong base (i.e. 15 Apr 05 returns 16 Apr 09). Close the workbook and
I then get the right date.

While this looks like a 1900 vs 1904 date system issue, I can't
replicate
this problem. If I enter a date in a cell in one workbook using 1904
date
system then access that cell in another workbook using 1900 date system
using either pull or a literal external reference link, I get a date 4
years and 1 day before what appears in the first workbook. If you get
something different, please show the actual contents of the date cell
in
your first workbook and the pull formula you're using to access it from
the second workbook.

IF YOU WANT HELP, **YOU** MUST SHOW FORMULAS AND DATA.

2. Can't actually auto update with Pull. Should it? Bit of a major
problem if it does not.

Do you mean check for changes in closed files? How would they change?
Are you accessing files on a network that you don't have open but other
users do and are constantly making changes? If you just mean updating
values from other workbooks when you open a file referring to ranges in
them, then if you have Calculation set to automatic, Excel recalcs the
pull calls even though it doesn't display the update dialog that it
displays when there are literal external reference links in the opened
workbook.

So what exactly do you mean by 'auto update'?

3. When copying and pasting to multiple cells, I must then enter
each cell formula and physicallly press the Enter key to update the
cell contents.

I can't replicate this behavior. If X:\Y\[foo.xls]Sheet1!A1:A10
contains the formula =ROW() in each cell, then entering the formula

=pull("'X:\Y\[foo.xls]Sheet1'!A"&ROW())

in [bar.xls]Sheet1!C1, copying and pasting this cell into
[bar.xls]Sheet1!C2:C10 results in {1;2;3;4;5;6;7;8;9;10} on my PC,
though it takes some time for the formulas to recalc. Does Excel
display the Calculate indicator in the status bar after you've
pasted cells containing pull calls? If so, you have to let Excel
recalc. Note that pull can take a while to recalc when called from
many cells. One thing it isn't is fast.

 

[Sandy]

Sandy wrote...
...

Have now made progress. Unfortunately, this has highlighted a couple
of problems:

1. If I pull a date from an open workbook, it responds with the
wrong base (i.e. 15 Apr 05 returns 16 Apr 09). Close the workbook and
I then get the right date.

While this looks like a 1900 vs 1904 date system issue, I can't
replicate
this problem. If I enter a date in a cell in one workbook using 1904
date
system then access that cell in another workbook using 1900 date system
using either pull or a literal external reference link, I get a date 4
years and 1 day before what appears in the first workbook. If you get
something different, please show the actual contents of the date cell
in
your first workbook and the pull formula you're using to access it from
the second workbook.

IF YOU WANT HELP, **YOU** MUST SHOW FORMULAS AND DATA.

2. Can't actually auto update with Pull. Should it? Bit of a major
problem if it does not.

Do you mean check for changes in closed files? How would they change?
Are you accessing files on a network that you don't have open but other
users do and are constantly making changes? If you just mean updating
values from other workbooks when you open a file referring to ranges in
them, then if you have Calculation set to automatic, Excel recalcs the
pull calls even though it doesn't display the update dialog that it
displays when there are literal external reference links in the opened
workbook.

So what exactly do you mean by 'auto update'?

3. When copying and pasting to multiple cells, I must then enter
each cell formula and physicallly press the Enter key to update the
cell contents.

I can't replicate this behavior. If X:\Y\[foo.xls]Sheet1!A1:A10
contains the formula =ROW() in each cell, then entering the formula

=pull("'X:\Y\[foo.xls]Sheet1'!A"&ROW())

in [bar.xls]Sheet1!C1, copying and pasting this cell into
[bar.xls]Sheet1!C2:C10 results in {1;2;3;4;5;6;7;8;9;10} on my PC,
though it takes some time for the formulas to recalc. Does Excel
display the Calculate indicator in the status bar after you've
pasted cells containing pull calls? If so, you have to let Excel
recalc. Note that pull can take a while to recalc when called from
many cells. One thing it isn't is fast.

Harlan,

First, let me clarify exactly where I am. The workbook with the
external references is used as a "management overview" of numerous
workbooks that are used by other staff on the network to update
customer returns - and indeed create new workbooks.

I have been using literal external links and associated formulas with
no problem. What I wanted to do was to be able to easily update the
"management" workbook simply without having to copy and paste full
filenames then modify for specific entries. Given that some of my
formulas have up to 4 nested If's, this is quite onerous.

Now to the behaviour I am presently experiencing:

1. The date issue does not occur if I use literal filenames. It
only occurs when I use the pull function and if the second workbook is
OPEN. When I close the workbook and then re-enter the formula,
everything is fine. The formula is as follows:

=pull("'"&$B$6&"["&A69&".xls]"&$A$3&"'!$g$2")

The cell value on the open/closed workbook is:

15/04/2005

I have deliberately removed the IF calculations to ensure that I get
same repeatable result.

2. The auto update I refer to is simply the automatic calculation
option (sorry about my terminology). This works fine if using full
path and filename but not if using the pull function, hence my
question. What I have seen is that if I copy and paste a pull formula
to multiple cells (for example B5 THRU B25), only the last cell is
calculated, even though all fields are updated correctly. Even if I
close the workbook, open it and update all links, the other cells are
not updated. I must physically enter each cell and update the formula
- not an ideal option.

I appreciate that I am asking a lot of your time but am running out of
ideas as to what can be wrong if you keep stating that all works fine
with your workbook. Am loathe to ditch this as your function
seems to be the answer to my problems (on paper at least!). Am I
missing something???

 

[Harlan Grove]

Sandy wrote...
....

Now to the behaviour I am presently experiencing:

1. The date issue does not occur if I use literal filenames. It
only occurs when I use the pull function and if the second workbook is
OPEN. When I close the workbook and then re-enter the formula,
everything is fine. The formula is as follows:

=pull("'"&$B$6&"["&A69&".xls]"&$A$3&"'!$g$2")

The cell value on the open/closed workbook is:

15/04/2005

I have deliberately removed the IF calculations to ensure that I get
same repeatable result.

Now I can replicate this if the source workbook uses the 1904 date
system. Specifically, Evaluate seems to return dates in the 1900 date
system all the time, and it automatically adjusts for dates in the
1904 date system. As far as I'm concerned, this is a bug in Excel/VBA,
but it's one I need to work around.

I'll post an update on my AOL ftp space tonight.

2. The auto update I refer to is simply the automatic calculation
option (sorry about my terminology). This works fine if using full
path and filename but not if using the pull function, hence my
question. What I have seen is that if I copy and paste a pull formula
to multiple cells (for example B5 THRU B25), only the last cell is
calculated, even though all fields are updated correctly. Even if I
close the workbook, open it and update all links, the other cells are
not updated. I must physically enter each cell and update the formula
- not an ideal option.

....

What do you mean by 'even though all fields are updated correctly'?

I'll assume you mean the argument to pull() is constructed correctly
for each call, but when you copy a cell containing a pull() call in
its formula and paste into other cells only one of those other cells
recalcs correctly. I can't replicate that behavior. If Calculation is
Automatic, then formulas calling pull() recalc automatically when
pasted into other cells. If Calculation is Manual, then they don't,
and you'd need to press [F9] to recalc.

If this just doesn't work in your formulas on your PC, there's a
quicker way of effectively re-entering a batch of formulas. Select
the range containing the formulas and use Edit > Replace to replace
all = with =, i.e., replace = with itself.

 

[Sandy]

Harlan,

Just thought that you may be interested in my progress. I have
managed to sort out the updating (my ignorance!). However, the
problem regarding dates within open and closed files remains. I have
also discovered that if filtering is used and automatic calculation is
selected then the time to re-calculate is significant (obviously
dependent upon the amount of workbooks being checked).

Both of the foregoing "problems" are not showstoppers and are only
given as information to you and also to others who may wish to use
your function.

In conclusion, I must say that the pull function saves me significant
time and effort and is now being used in "anger". Many thanks for
your help and patience.

Sandy