Up date links problem

  • Thread starter Thread starter brees003
  • Start date Start date
B

brees003

I'm trying to use a 'sumif' function with links in it as follows:

=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:$D$208,T(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$G$9:$G$208)

This brings the correct values through fine when the sub sheet is open,
but if you try and open the sheet with this function in, it returns
#value if the sub sheet is not open.

Hope someone can help as this is driving me flam'n crazy.

Cheers
 
Check if you have the "ask to update automatic links"
option checked. It's under tools, edit. Check the box and
it'll ask you if you want to update the links. Say no and
it'll keep the last numbers it linked to.
-----Original Message-----
I'm trying to use a 'sumif' function with links in it as follows:
=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003
Click to expand...
\[2003.09.30.xls]costings'!$D$9:$D$208,T
(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003
\[2003.09.30.xls]costings'!$G$9:$G$208)

This brings the correct values through fine when the sub sheet is open,
but if you try and open the sheet with this function in, it returns
#value if the sub sheet is not open.

Hope someone can help as this is driving me flam'n crazy.

Cheers



------------------------------------------------

~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
 
Sumif() doesn't work on closed files, as you've discovered.

However Sum() does work on closed WBs, and so does IF().

So rewrite your formula something like this:

=IF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:$D$
208=T(E11),SUM('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costing
s'!$G$9:$G$208),"")
--

HTH,

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

I'm trying to use a 'sumif' function with links in it as follows:

=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:
$D$208,T(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings
'!$G$9:$G$208)

This brings the correct values through fine when the sub sheet is open,
but if you try and open the sheet with this function in, it returns
#value if the sub sheet is not open.

Hope someone can help as this is driving me flam'n crazy.

Cheers
 
I like RagDyer's idea of using Sum and If, but in an array formula:

=SUM(IF('yourlongpath\[2003.09.30.xls]costings'!$D$9:$D$208=T(E11),
'yourlongpath\[2003.09.30.xls]costings'!$G$9:$G$208))

(still one cell. Notice the sum(if( and the =T(E11) changes and extra closing
parenthesis.)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(I shortened the path so that it looked prettier in the newsgroup posting.)
I'm trying to use a 'sumif' function with links in it as follows:

=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:$D$208,T(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$G$9:$G$208)

This brings the correct values through fine when the sub sheet is open,
but if you try and open the sheet with this function in, it returns
#value if the sub sheet is not open.

Hope someone can help as this is driving me flam'n crazy.

Cheers
Click to expand...
 
What are the advantages Dave, of using an array formula ?

From what I've read in these NGs, they're less efficient then other types of
formulas, and since your formula works without being array entered, I'd like
to know the benefits ... please.
--


Regards,

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


And, you

I like RagDyer's idea of using Sum and If, but in an array formula:

=SUM(IF('yourlongpath\[2003.09.30.xls]costings'!$D$9:$D$208=T(E11),
'yourlongpath\[2003.09.30.xls]costings'!$G$9:$G$208))

(still one cell. Notice the sum(if( and the =T(E11) changes and extra
closing
parenthesis.)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

(I shortened the path so that it looked prettier in the newsgroup posting.)
I'm trying to use a 'sumif' function with links in it as follows:

=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:
$D$208,T(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings
'!$G$9:$G$208)

This brings the correct values through fine when the sub sheet is open,
but if you try and open the sheet with this function in, it returns
#value if the sub sheet is not open.

Hope someone can help as this is driving me flam'n crazy.

Cheers
Click to expand...
 
That formula may have returned a number, but I don't think it was correct
(except by chance). Go back and try it using ctrl-shift-enter and see if the
value changed. (if it didn't modify your test data a little.)

The biggest benefit was that it worked with a closed workbook when =sumif()
didn't <bg>.

I like to think of the array formulas as doing "for each cell in range, do
something". You could always use a (few?) helper columns to get the same
effect, but sometimes one formula in one cell is actually easier to understand.

Chip Pearson has a bunch of notes on array formulas at:
http://www.cpearson.com/excel/array.htm

(and if you really want to get a headache, take a trip over to the
..worksheet.functions group and watch Harlan, Peo, Dan, Aladin, and too many more
to mention answer a few questions.)

(Headache nothing. My head explodes!)


What are the advantages Dave, of using an array formula ?

From what I've read in these NGs, they're less efficient then other types of
formulas, and since your formula works without being array entered, I'd like
to know the benefits ... please.
--

Regards,

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

And, you

I like RagDyer's idea of using Sum and If, but in an array formula:

=SUM(IF('yourlongpath\[2003.09.30.xls]costings'!$D$9:$D$208=T(E11),
'yourlongpath\[2003.09.30.xls]costings'!$G$9:$G$208))

(still one cell. Notice the sum(if( and the =T(E11) changes and extra
closing
parenthesis.)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

(I shortened the path so that it looked prettier in the newsgroup posting.)
I'm trying to use a 'sumif' function with links in it as follows:

=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:
$D$208,T(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings
'!$G$9:$G$208)

This brings the correct values through fine when the sub sheet is open,
but if you try and open the sheet with this function in, it returns
#value if the sub sheet is not open.

Hope someone can help as this is driving me flam'n crazy.

Cheers
Click to expand...
Click to expand...
 
Yep, you're right !

It does need the Sum(If ... and CSE.
I should have done a little (lot) more testing.
--


Regards,

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

That formula may have returned a number, but I don't think it was correct
(except by chance). Go back and try it using ctrl-shift-enter and see if
the
value changed. (if it didn't modify your test data a little.)

The biggest benefit was that it worked with a closed workbook when =sumif()
didn't <bg>.

I like to think of the array formulas as doing "for each cell in range, do
something". You could always use a (few?) helper columns to get the same
effect, but sometimes one formula in one cell is actually easier to
understand.

Chip Pearson has a bunch of notes on array formulas at:
http://www.cpearson.com/excel/array.htm

(and if you really want to get a headache, take a trip over to the
..worksheet.functions group and watch Harlan, Peo, Dan, Aladin, and too many
more
to mention answer a few questions.)

(Headache nothing. My head explodes!)


What are the advantages Dave, of using an array formula ?

From what I've read in these NGs, they're less efficient then other types of
formulas, and since your formula works without being array entered, I'd like
to know the benefits ... please.
--

Regards,

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

And, you

I like RagDyer's idea of using Sum and If, but in an array formula:

=SUM(IF('yourlongpath\[2003.09.30.xls]costings'!$D$9:$D$208=T(E11),
'yourlongpath\[2003.09.30.xls]costings'!$G$9:$G$208))

(still one cell. Notice the sum(if( and the =T(E11) changes and extra
closing
parenthesis.)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

(I shortened the path so that it looked prettier in the newsgroup posting.)
I'm trying to use a 'sumif' function with links in it as follows:
Click to expand...
=SUMIF('C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings'!$D$9:$D$208,T(E11),'C:\BRS\Lend\WP\CMT\Project\Time\2003\[2003.09.30.xls]costings
Click to expand...
 
Back
Top