cross linking excel files - help needed!

[Sastul]

ok, there is a nut that i cant break.

I have 2-3 excel files that need to be linked.
File M (master) contains multiple worksheets, of all sorts of data, its
the master file for my files
File L, is a lookup file im creating for better usage, its used for
dumping data from msproject and reformat some stuff. It has only one
sheet of relevance.
File S, the summary file is the thing i need help with, it is linked to
both files and is used to create monthly summarys. In that file i am
using a lot SUMIF elements like:
SUMIF ([file M]sheet1!A1:A30;B8;[file M]sheet1!D130)

Now the thing is: This works fine aslong as i have L and M files open
or when i had them open AFTER i opend the S file.
If i only open the S file i get a #VALUE! in all fields where i have
SUMIF commands. This #VALUE! is replaced with the proper value as soon
as i open the files entered in the SUMIF.

How can that be solved?

 

[Dave Peterson]

I think =sumif() dislikes closed files.

But maybe you could rewrite your formula using:
=SUM(IF('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8,
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30))
(all one cell)

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'd actually create the formula with both workbooks open, so I could just point
and click on the ranges. Then when you close the "file M" workbook, you'll see
the full path to the closed file.)

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)

ok, there is a nut that i cant break.

I have 2-3 excel files that need to be linked.
File M (master) contains multiple worksheets, of all sorts of data, its
the master file for my files
File L, is a lookup file im creating for better usage, its used for
dumping data from msproject and reformat some stuff. It has only one
sheet of relevance.
File S, the summary file is the thing i need help with, it is linked to
both files and is used to create monthly summarys. In that file i am
using a lot SUMIF elements like:
SUMIF ([file M]sheet1!A1:A30;B8;[file M]sheet1!D130)

Now the thing is: This works fine aslong as i have L and M files open
or when i had them open AFTER i opend the S file.
If i only open the S file i get a #VALUE! in all fields where i have
SUMIF commands. This #VALUE! is replaced with the proper value as soon
as i open the files entered in the SUMIF.

How can that be solved?

 

[Peo Sjoblom]

Or use sumproduct and enter normally

=SUMPRODUCT(--('C:\My Documents\excel\[file
m.xls]Sheet1'!$A$1:$A$30=D8),'C:\My Documents\excel\[file
m.xls]Sheet1'!$D$1:$D$30)

Beware of the line wrap

--

Regards,

Peo Sjoblom

I think =sumif() dislikes closed files.

But maybe you could rewrite your formula using:
=SUM(IF('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8,
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30))
(all one cell)

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'd actually create the formula with both workbooks open, so I could just point
and click on the ranges. Then when you close the "file M" workbook, you'll see
the full path to the closed file.)

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)

ok, there is a nut that i cant break.

I have 2-3 excel files that need to be linked.
File M (master) contains multiple worksheets, of all sorts of data, its
the master file for my files
File L, is a lookup file im creating for better usage, its used for
dumping data from msproject and reformat some stuff. It has only one
sheet of relevance.
File S, the summary file is the thing i need help with, it is linked to
both files and is used to create monthly summarys. In that file i am
using a lot SUMIF elements like:
SUMIF ([file M]sheet1!A1:A30;B8;[file M]sheet1!D130)

Now the thing is: This works fine aslong as i have L and M files open
or when i had them open AFTER i opend the S file.
If i only open the S file i get a #VALUE! in all fields where i have
SUMIF commands. This #VALUE! is replaced with the proper value as soon
as i open the files entered in the SUMIF.

How can that be solved?

 

[Dave Peterson]

Hmmm.

near the bottom of my post:

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)


=====
<vbg>

ps. I'm getting delays on an email sent directly to you. Is your mailbox full?

Or use sumproduct and enter normally

=SUMPRODUCT(--('C:\My Documents\excel\[file
m.xls]Sheet1'!$A$1:$A$30=D8),'C:\My Documents\excel\[file
m.xls]Sheet1'!$D$1:$D$30)

Beware of the line wrap

--

Regards,

Peo Sjoblom

I think =sumif() dislikes closed files.

But maybe you could rewrite your formula using:
=SUM(IF('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8,
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30))
(all one cell)

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'd actually create the formula with both workbooks open, so I could just point
and click on the ranges. Then when you close the "file M" workbook, you'll see
the full path to the closed file.)

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)

ok, there is a nut that i cant break.

I have 2-3 excel files that need to be linked.
File M (master) contains multiple worksheets, of all sorts of data, its
the master file for my files
File L, is a lookup file im creating for better usage, its used for
dumping data from msproject and reformat some stuff. It has only one
sheet of relevance.
File S, the summary file is the thing i need help with, it is linked to
both files and is used to create monthly summarys. In that file i am
using a lot SUMIF elements like:
SUMIF ([file M]sheet1!A1:A30;B8;[file M]sheet1!D130)

Now the thing is: This works fine aslong as i have L and M files open
or when i had them open AFTER i opend the S file.
If i only open the S file i get a #VALUE! in all fields where i have
SUMIF commands. This #VALUE! is replaced with the proper value as soon
as i open the files entered in the SUMIF.

How can that be solved?

 

[Peo Sjoblom]

LOL!

That's what I get for reading the whole post..

I have no idea but I believe the mvps.org
is spammed very hard and I have problems receiving emails. If you need to
email me you can use

(e-mail address removed)


--

Regards,

Peo Sjoblom

Hmmm.

near the bottom of my post:

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)


=====
<vbg>

ps. I'm getting delays on an email sent directly to you. Is your mailbox full?

Or use sumproduct and enter normally

=SUMPRODUCT(--('C:\My Documents\excel\[file
m.xls]Sheet1'!$A$1:$A$30=D8),'C:\My Documents\excel\[file
m.xls]Sheet1'!$D$1:$D$30)

Beware of the line wrap

--

Regards,

Peo Sjoblom

I think =sumif() dislikes closed files.

But maybe you could rewrite your formula using:
=SUM(IF('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8,
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30))
(all one cell)

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'd actually create the formula with both workbooks open, so I could

just
point

and click on the ranges. Then when you close the "file M" workbook, you'll see
the full path to the closed file.)

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)


Sastul wrote:

ok, there is a nut that i cant break.

I have 2-3 excel files that need to be linked.
File M (master) contains multiple worksheets, of all sorts of data, its
the master file for my files
File L, is a lookup file im creating for better usage, its used for
dumping data from msproject and reformat some stuff. It has only one
sheet of relevance.
File S, the summary file is the thing i need help with, it is linked to
both files and is used to create monthly summarys. In that file i am
using a lot SUMIF elements like:
SUMIF ([file M]sheet1!A1:A30;B8;[file M]sheet1!D130)

Now the thing is: This works fine aslong as i have L and M files open
or when i had them open AFTER i opend the S file.
If i only open the S file i get a #VALUE! in all fields where i have
SUMIF commands. This #VALUE! is replaced with the proper value as soon
as i open the files entered in the SUMIF.

How can that be solved?

 

[Sastul]

works great!
that shift+ctrl+enter did the trick

many thanks!