PULL Function

[Sandy]

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!

 

[Harlan Grove]

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]

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.

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.

 

[Harlan Grove]

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]

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!

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

 

[Harlan Grove]

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]

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]

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]

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

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.

 

[Harlan Grove]

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]

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]

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.