SUMIF Returns a #VALUE error when external source is closed

G

ghynes

Can you help me out with this? if works fine if the external source i
open but returns a #value if its closed. is there anyway of correctin
this
 
J

Julieeeee

When you open that file, it should ask you if you want to update - choose "do
not update" otherwise you will have to open the other file too.
 
F

FSt1

hi,
post your sumif formula. need more info that you profided. we are not
psychics.

Regards
FSt1
 
G

Gidders

I'm having the same issue. I have one sheet which contains the data I want to
sum, dependant on the data held in another sheet, in this case [CSS Input] eg

=SUMIF('[CSS Input.xls]Input'!$T3:$T151,"yes",C11:C151)/'[CSS
Input.xls]Input'!$V$2

If the sheet CSS Input is open at the same time as the sheet I'm working on
then the formula works. If however I just open the main file, I'm propmted to
update the links to external data sources & if I click update (because CSS
Input might have been amended independently), it returns #value.

The formula now reads formula reads

=SUMIF('C:\Documents and Settings\1358745\My Documents\[CSS
Input.xls]Input'!$T3:$T151,"yes",C11:C151)/'C:\Documents and
Settings\1358745\My Documents\[CSS Input.xls]Input'!$V$2

I've never had problems with other links to external sheets and in fact the
straight reference to [CSS Input.xls]Input'!$V$2 works fine

Any ideas?
Gidders
 
D

Domenic

Try using SUMPRODUCT...

=SUMPRODUCT(--('C:\Documents and Settings\1358745\My Documents\[CSS
Input.xls]Input'!$T3:$T151="Yes"),'C:\Documents and Settings\1358745\My
Documents\[CSS Input.xls]Input'!$C3:$C151)/'C:\Documents and
Settings\1358745\My Documents\[CSS Input.xls]Input'!$V$2

Note the your ranges need to be the same size.

Hope this helps!
 
G

Gidders

Thanks for that - it works great.

By the way, what does the --(expession) syntax do?

Domenic said:
Try using SUMPRODUCT...

=SUMPRODUCT(--('C:\Documents and Settings\1358745\My Documents\[CSS
Input.xls]Input'!$T3:$T151="Yes"),'C:\Documents and Settings\1358745\My
Documents\[CSS Input.xls]Input'!$C3:$C151)/'C:\Documents and
Settings\1358745\My Documents\[CSS Input.xls]Input'!$V$2

Note the your ranges need to be the same size.

Hope this helps!

Gidders said:
I'm having the same issue. I have one sheet which contains the data I want to
sum, dependant on the data held in another sheet, in this case [CSS Input] eg

=SUMIF('[CSS Input.xls]Input'!$T3:$T151,"yes",C11:C151)/'[CSS
Input.xls]Input'!$V$2

If the sheet CSS Input is open at the same time as the sheet I'm working on
then the formula works. If however I just open the main file, I'm propmted to
update the links to external data sources & if I click update (because CSS
Input might have been amended independently), it returns #value.

The formula now reads formula reads

=SUMIF('C:\Documents and Settings\1358745\My Documents\[CSS
Input.xls]Input'!$T3:$T151,"yes",C11:C151)/'C:\Documents and
Settings\1358745\My Documents\[CSS Input.xls]Input'!$V$2

I've never had problems with other links to external sheets and in fact the
straight reference to [CSS Input.xls]Input'!$V$2 works fine

Any ideas?
Gidders
Click to expand...
Click to expand...
 
Top