"Harlan Grove" wrote in message roups.com...
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???
|