Hi All,

We're running 2003 and when I open files with external references, it
can't update them unless they are open on my machine. The files are
on a network - any reason why they won't update if the files are
closed? If this is normal, is there a work-around?

Many thanks,

Thomas.

What formulas are you using?

Some functions *can* work on closed files, while others cannot.

For example:
Sumif and Countif and Indirect *cannot*, while the array set-up of =Sum(If
... .) *can*.

Post some of your formulas.
--

Regards,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

wrote in message
ups.com...
Hi All,

We're running 2003 and when I open files with external references, it
can't update them unless they are open on my machine. The files are
on a network - any reason why they won't update if the files are
closed? If this is normal, is there a work-around?

Many thanks,

Thomas.

On May 5, 1:53 pm, "RagDyeR" wrote:
What formulas are you using?

Some functions *can* work on closed files, while others cannot.

For example:
Sumif and Countif and Indirect *cannot*, while the array set-up of =Sum(If
.. .) *can*.

Post some of your formulas.

Thanks RD,

Unfortunately, the majority are of the Sumif variety, with a few
simple = formulas.

For example

=SUMIF('\\Desktop1\Timesheets\[TP 2005.xls]January'!$C$15:$C$300,$B
$4,'\\Desktop1\Timesheets\[TP 2005.xls]January'!$D$15:$D$300)

We're drawing together everyone's time sheets across every year and
breaking the information back down into projects, for billing
purposes. There are nearly 100 projects, eight staff, three years,
etc. The staff timesheets must be individual as they are confidential
and are often open at the same time (our system automatically opens
them every morning for each member of staff). The projects could
potentially be combined in one spreadsheet, but it would be
gargantuan, given that most projects run 36+ months, are billed
monthly and it is easiest to have the monthly sheets printable. The
other option would be to combine them with a sheet per project and a
dense table of all data. We could then break out months not directly
from Excel but rather using a print-merge.

Really, we'd just rather everything could update through - any
workarounds other than having 325 worksheets open at once?

Thanks,

Thomas.

I guess *YOU DIDN'T FOLLOW* when I said:
<<<"while the array set-up of =Sum(If .. .) *can*.(work)"

Try this *array* formula:

=SUM(IF('\\Desktop1\Timesheets\[TP
2005.xls]January'!$C$15:$C$300=$B$4,'\\Desktop1\Timesheets\[TP
2005.xls]January'!$D$15:$D$300))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message
oups.com...
On May 5, 1:53 pm, "RagDyeR" wrote:
What formulas are you using?

Some functions *can* work on closed files, while others cannot.

For example:
Sumif and Countif and Indirect *cannot*, while the array set-up of
=Sum(If
.. .) *can*.

Post some of your formulas.

Thanks RD,

Unfortunately, the majority are of the Sumif variety, with a few
simple = formulas.

For example

=SUMIF('\\Desktop1\Timesheets\[TP 2005.xls]January'!$C$15:$C$300,$B
$4,'\\Desktop1\Timesheets\[TP 2005.xls]January'!$D$15:$D$300)

We're drawing together everyone's time sheets across every year and
breaking the information back down into projects, for billing
purposes. There are nearly 100 projects, eight staff, three years,
etc. The staff timesheets must be individual as they are confidential
and are often open at the same time (our system automatically opens
them every morning for each member of staff). The projects could
potentially be combined in one spreadsheet, but it would be
gargantuan, given that most projects run 36+ months, are billed
monthly and it is easiest to have the monthly sheets printable. The
other option would be to combine them with a sheet per project and a
dense table of all data. We could then break out months not directly
from Excel but rather using a print-merge.

Really, we'd just rather everything could update through - any
workarounds other than having 325 worksheets open at once?

Thanks,

Thomas.

On May 6, 1:48 pm, "Ragdyer" wrote:
I guess *YOU DIDN'T FOLLOW* when I said:
<<<"while the array set-up of =Sum(If .. .) *can*.(work)"

Try this *array* formula:

=SUM(IF('\\Desktop1\Timesheets\[TP
2005.xls]January'!$C$15:$C$300=$B$4,'\\Desktop1\Timesheets\[TP
2005.xls]January'!$D$15:$D$300))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

oups.com...

On May 5, 1:53 pm, "RagDyeR" wrote:
What formulas are you using?

Some functions *can* work on closed files, while others cannot.

For example:
Sumif and Countif and Indirect *cannot*, while the array set-up of
=Sum(If
.. .) *can*.

Post some of your formulas.

Thanks RD,

Unfortunately, the majority are of the Sumif variety, with a few
simple = formulas.

For example

=SUMIF('\\Desktop1\Timesheets\[TP 2005.xls]January'!$C$15:$C$300,$B
$4,'\\Desktop1\Timesheets\[TP 2005.xls]January'!$D$15:$D$300)

We're drawing together everyone's time sheets across every year and
breaking the information back down into projects, for billing
purposes. There are nearly 100 projects, eight staff, three years,
etc. The staff timesheets must be individual as they are confidential
and are often open at the same time (our system automatically opens
them every morning for each member of staff). The projects could
potentially be combined in one spreadsheet, but it would be
gargantuan, given that most projects run 36+ months, are billed
monthly and it is easiest to have the monthly sheets printable. The
other option would be to combine them with a sheet per project and a
dense table of all data. We could then break out months not directly
from Excel but rather using a print-merge.

Really, we'd just rather everything could update through - any
workarounds other than having 325 worksheets open at once?

Thanks,

Thomas.

That is superb, thank you very much.

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

wrote in message
ups.com...
On May 6, 1:48 pm, "Ragdyer" wrote:
I guess *YOU DIDN'T FOLLOW* when I said:
<<<"while the array set-up of =Sum(If .. .) *can*.(work)"

Try this *array* formula:

=SUM(IF('\\Desktop1\Timesheets\[TP
2005.xls]January'!$C$15:$C$300=$B$4,'\\Desktop1\Timesheets\[TP
2005.xls]January'!$D$15:$D$300))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

oups.com...

On May 5, 1:53 pm, "RagDyeR" wrote:
What formulas are you using?

Some functions *can* work on closed files, while others cannot.

For example:
Sumif and Countif and Indirect *cannot*, while the array set-up of
=Sum(If
.. .) *can*.

Post some of your formulas.

Thanks RD,

Unfortunately, the majority are of the Sumif variety, with a few
simple = formulas.

For example

=SUMIF('\\Desktop1\Timesheets\[TP 2005.xls]January'!$C$15:$C$300,$B
$4,'\\Desktop1\Timesheets\[TP 2005.xls]January'!$D$15:$D$300)

We're drawing together everyone's time sheets across every year and
breaking the information back down into projects, for billing
purposes. There are nearly 100 projects, eight staff, three years,
etc. The staff timesheets must be individual as they are confidential
and are often open at the same time (our system automatically opens
them every morning for each member of staff). The projects could
potentially be combined in one spreadsheet, but it would be
gargantuan, given that most projects run 36+ months, are billed
monthly and it is easiest to have the monthly sheets printable. The
other option would be to combine them with a sheet per project and a
dense table of all data. We could then break out months not directly
from Excel but rather using a print-merge.

Really, we'd just rather everything could update through - any
workarounds other than having 325 worksheets open at once?

Thanks,

Thomas.

That is superb, thank you very much.