PULL Function

Hello,
I am trying to use Harlan Groves PULL UDF. I have inserted a module in my
work book and added the code verbatim as posted on his site. My indirect
function is:
=INDIRECT("'[FFPSTotal "& YEAR &".xls]" &$R9&"'!B$13")
This works of course only when FFPS is open. How do I modify the formula to
incorporate PULL. I have tried PULL("'[FFPSTotal "& YEAR &".xls]"
&$R9&"'!B$13"), and get #REF!
Thanks!

Sandy wrote...
....
>This works of course only when FFPS is open. How do I modify the formula to
>incorporate PULL. I have tried PULL("'[FFPSTotal "& YEAR &".xls]"
>&$R9&"'!B$13"), and get #REF!

You need to include the full drive/directory path. It's necessary when
the workbook is closed, and it does no harm when the workbook is open.

Thanks! that got rid of the #ref! but if I update the refernced cell, the
change doesnt make it over to the other workbook. How do I update the values.



"Harlan Grove" wrote:

> Sandy wrote...
> ....
> >This works of course only when FFPS is open. How do I modify the formula to
> >incorporate PULL. I have tried PULL("'[FFPSTotal "& YEAR &".xls]"
> >&$R9&"'!B$13"), and get #REF!
>
> You need to include the full drive/directory path. It's necessary when
> the workbook is closed, and it does no harm when the workbook is open.
>
>

Sandy wrote...
>Thanks! that got rid of the #ref! but if I update the refernced cell, the
>change doesnt make it over to the other workbook. How do I update the values.
....

If the other file is closed, you REALLY don't want pull to be volatile.
If the other file is open, you should be using INDIRECT (which can also
accept the full drive/directory path, though it does nothing). If you
mean you're linking to a file not open on your machine, but open and
updated (resaved) by someone else, and you want your external
references refreshed when any changes are made, that's difficult and
involves monitoring the file's modification date periodically. If all
you want to do is force recalculation, hold down [Shift] and [Ctrl]
keys before pressing [F9].

Thanks for the reply
I guess I am confused now. I thought that the purpose of the function was
to get data froma closed workbook in the same manner as INDIRECT. I recieve
a spreadsheetvia email evry month, that I save on my hard drive. I use some
of the data on the sheet in my workbook. I thought PULL would be the best
way to accomplish the task. Is there a better way?
Thanks!

"Harlan Grove" wrote:

> Sandy wrote...
> >Thanks! that got rid of the #ref! but if I update the refernced cell, the
> >change doesnt make it over to the other workbook. How do I update the values.
> ....
>
> If the other file is closed, you REALLY don't want pull to be volatile.
> If the other file is open, you should be using INDIRECT (which can also
> accept the full drive/directory path, though it does nothing). If you
> mean you're linking to a file not open on your machine, but open and
> updated (resaved) by someone else, and you want your external
> references refreshed when any changes are made, that's difficult and
> involves monitoring the file's modification date periodically. If all
> you want to do is force recalculation, hold down [Shift] and [Ctrl]
> keys before pressing [F9].
>
>

Sandy wrote...
>Thanks for the reply
>I guess I am confused now. I thought that the purpose of the function was
>to get data froma closed workbook in the same manner as INDIRECT. I recieve
>a spreadsheetvia email evry month, that I save on my hard drive. I use some
>of the data on the sheet in my workbook. I thought PULL would be the best
>way to accomplish the task. Is there a better way?
....

pull could do this depending on your calculation setting. If
calculation is set to Automatic, then every workbook you open
recalculates upon opening. That should refresh pull's results, at least
it does so on my machine. If you're leaving the workbook containing the
pull calls open all the time, so never reopening it, you'd need a
different way of forcing full recalculation.

So how were you using pull but not seeing changes when the other
workbook changed?

I am exploring the use of Longre's MoreFunc at this time but I would rather
use the PULL approach rath than having to distribute the add-in Here is
thecurrent set up

The source data is in workbook A and the PULL function is in workbook B.
With A open and B closed I modify a target cell. Save changes and close.
Open B and the only way that I can get the PULL formula to update the
modified value is to F2 in each cell that contains the formula. Calculation
is set to auto and ctrl shift F9 does not update.
Thanks!


"Harlan Grove" wrote:

> Sandy wrote...
> >Thanks for the reply
> >I guess I am confused now. I thought that the purpose of the function was
> >to get data froma closed workbook in the same manner as INDIRECT. I recieve
> >a spreadsheetvia email evry month, that I save on my hard drive. I use some
> >of the data on the sheet in my workbook. I thought PULL would be the best
> >way to accomplish the task. Is there a better way?
> ....
>
> pull could do this depending on your calculation setting. If
> calculation is set to Automatic, then every workbook you open
> recalculates upon opening. That should refresh pull's results, at least
> it does so on my machine. If you're leaving the workbook containing the
> pull calls open all the time, so never reopening it, you'd need a
> different way of forcing full recalculation.
>
> So how were you using pull but not seeing changes when the other
> workbook changed?
>
>

Sandy wrote...
....
>The source data is in workbook A and the PULL function is in workbook B.
>With A open and B closed I modify a target cell. Save changes and close.
>Open B and the only way that I can get the PULL formula to update the
>modified value is to F2 in each cell that contains the formula. Calculation
>is set to auto and ctrl shift F9 does not update.
....

I can't reproduce this behavior. When I open files containing pull
calls, Excel recalculates the formulas calling pull. When I open
workbooks A and B separately in separate Excel instances (using
Windows's Start menu to launch Excel twice), then change entries in A,
B doesn't automatically reflect those changes (there's no internal
Excel event triggered by the other Excel instance changing the A file
on disk), but it does update when I press [Ctrl]+[Shift]+[F9].

Do you have a lot of pull calls?

If Longre's add-in works better, use it. The latest version allows you
to embed it in your own workbooks, so you shouldn't have to distribute
anything more than your .xls file containing the embedded add-in.

What do I do to embed as you say?
Thanks!

"Harlan Grove" wrote:

> Sandy wrote...
> ....
> >The source data is in workbook A and the PULL function is in workbook B.
> >With A open and B closed I modify a target cell. Save changes and close.
> >Open B and the only way that I can get the PULL formula to update the
> >modified value is to F2 in each cell that contains the formula. Calculation
> >is set to auto and ctrl shift F9 does not update.
> ....
>
> I can't reproduce this behavior. When I open files containing pull
> calls, Excel recalculates the formulas calling pull. When I open
> workbooks A and B separately in separate Excel instances (using
> Windows's Start menu to launch Excel twice), then change entries in A,
> B doesn't automatically reflect those changes (there's no internal
> Excel event triggered by the other Excel instance changing the A file
> on disk), but it does update when I press [Ctrl]+[Shift]+[F9].
>
> Do you have a lot of pull calls?
>
> If Longre's add-in works better, use it. The latest version allows you
> to embed it in your own workbooks, so you shouldn't have to distribute
> anything more than your .xls file containing the embedded add-in.
>
>

Sandy wrote...
>What do I do to embed as you say?
....

Read the MOREFUNC.XLL help topic titled 'Including Morefunc in a
workbook' and follow the directions.

Thanks!
I have read but I do not see morefunc on the TOOLS menu. I have uninstalled
and reinstalled the add-in and I have ensured that the box was checked to add
to the menu, but it is still not there. Other add-ins do show there bit not
morefunc
Thanks

"Harlan Grove" wrote:

> Sandy wrote...
> >What do I do to embed as you say?
> ....
>
> Read the MOREFUNC.XLL help topic titled 'Including Morefunc in a
> workbook' and follow the directions.
>
>

Sandy wrote...
>I have read but I do not see morefunc on the TOOLS menu. I have uninstalled
>and reinstalled the add-in and I have ensured that the box was checked to add
>to the menu, but it is still not there. Other add-ins do show there bit not
>morefunc
....

You may need to uninstall and reinstall MOREFUNC. The Morefunc menu
under Tools is an installation option. It may not be checked by
default, in which case you'd need to check it during installation.

While I'm sure Sandy has found the solution by now, if anyone else comes across this posting and is having the same issue, the option to use is Ctrl+Alt+Shift+F9

A big thanks to Harlan for an awesome function!

Kerry.

Hi Kerry,

Many thanks for the post. I came across exactly the same problem as Sandy, and Ctrl+Alt+Shift+F9 was the only thing that did the trick.

This could also be embedded in a module as follows:

Sub CellCalc()

Sheets("Sheet1").Select
Application.Calculate

End Sub

... but this is not always reliable.

I noticed that in my case, when opening the sheet, I would always have to click "check status" of links, as these were always displaying as "unknown"

If anyone could suggest a better way to invoke a recalculation of cells automatically upon opening a worksheet, then please add it to this thread.

xlcoder

... actually, a better way may be to use the following code:


Sub CellCalc2()

Application.SendKeys "%^{F9}"
'sends keystrokes for ctrl+alt+F9 to the keyboard buffer

DoEvents

End Sub

I am using excel 2010, and experiencing similiar issues. Basically the only way I can get the pull function to update is to hit enter after "editing" a cell containing the pull function- this then updates all pull functions on the sheet. Any help would be greatly appreciated!

Just wanted to say a big thank you for this macro and the update sequences, was worried for a minute there! , cntrl alt shift F9 :DD