one "not friendly" way to handle closed books generated by others is to have
a sheet which duplicates the info in your book
goto the source book
select all in the sheet of interest
go to your book insert a worksheet selct cell A1 and paste special link
you can then do all of the claulations you want on your book alone. If it
is ssensitive information which you don't wnat people to look at
further select all on your copy sheet and format cells number custom ;;; and
things on the sheet will not be seen

as always it will keep most honest people honest but is not secure against a
dedicated "Player"

"Margie" wrote:

Yes they are so i'm sure that is my problem. TB is the entire database so it
can look for the product i want in column A while TBTL is the total column
only so it would add up the product lines if found in A with the totals in L
only. The columns B-K also have $ values and the file comes prepared to me
by our IS dept so i don't have flexibility in what is in the files. i did
know i would have to redo the ranges so i do have TB & TBTL changes from L:L
to L1:L2000 etc. i'm thinking i've hit another roadblock on this one, but it
seems like i should be able to work around it. problem is the product i want
it to find is on the active workbook on another sheet then it has to find
those applicable lines in the workbook i don't want to have to keep open.

"Peo Sjoblom" wrote:

Since you are using named ranges I expect that they are not of the same
size, that would explain the num errors, in SUMPRODUCT the ranges need to be
of the same size so I suspect that TB and TBTL are of a different size.

It should otherwise work, I have used it many times without any problems


--
Regards,

Peo Sjoblom

"Margie" wrote in message
...
I've tried it several ways and can't seem to be able to use this
SUMPRODUCT -
either getting wrong numbers or #NUM error. my orig formula looks at a
table
and finds a value then only would add up if it found that value on the
second
table the $"s in the total column. It seems to be adding up the entire
column or giving me the error. suggestions?

"Peo Sjoblom" wrote:

Yes it does but sumproduct can do what you want, post the formula you
have
when the source files are open, since Excel will put the path when it is
closed it is easier (less to type etc. for us to help you

For instance


=SUMIF(A2:A1000,C2,B2:B1000)

will sum B when A equals the value in C2

the equivalent using SUMPRODUCT would look like


=SUMPRODUCT(--(A2:A1000=C2),B2:B1000)

and it will work when the source book is closed, the drawback is that it
is
slower than SUMIF
since it is a de facto array formula albeit not entered as one


--
Regards,

Peo Sjoblom



"Margie" wrote in message
...
do you know if excel 2007 still requires that the source file be open?

"Margie" wrote:

thanks for the information; doesn't make me happy, but it explains it
anyway.

"bj" wrote:

Sumif needs to have the external file open, as does countif and
several
other
functions. I do not have a list of all of the which need to have the
other
file open.

"Margie" wrote:

getting #Value error in cell if source file isn't open when using
SUMIF
formula. Once file is opened the correct $$/amounts appear and
there
are no
errors. why am i getting value errors using this formula??? see
example of
formula i am questioning below.

=SUMIF('R:\Signature\ATB\atb seq 23
Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23
Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23
Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23
Jun07.xls'!TBTL)