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?