Hi Dave. Thank you. I'm fine with having to open both workbooks. However
even when both workbooks are open, the links don't update for the remote
user. We're both going to the same files, only getting there a differnt way.
When i create the file and input the formula, it works for me, it paths to
the file it's trying to read and does fine. However, when someone who opens
the file from a different office, the formula can't find the file with the
path i input because it's not me accessing. The files havent' been moved.
Make sense (i hope).

"Dave Peterson" wrote:

There are some worksheet functions that won't work with closed workbooks--but
IIRC, most of them would return #ref! errors.

=sumif(), =countif(), =indirect()

Are a few.

But there are replacement functions that will work:

I'd create this formula with the sending workbook open so that excel can do the
heavy lifting and fix the path when you close that sending workbook

=if(sumproduct(--('[RIS Compltd.xls]RIS Completed'!$B1:$B1000=D12))0,
"yes","no")

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Suzanne wrote:

I have 2 Excel files, sitting in the same folder. There is a formula in file
A that looks for a value in file B and updates file A accordingly. The
formula works like a charm when users in my office open file A, select update
links, etc. However, when 'remote' users attempt to go thru the same
motions, the links error and wont update. I'm sure it's due to the pathing
embedded in the formula that gets the user to file B but to no avail have
solved for it. Any suggestions. There's got to be a way to make this work.

Remote open:
=IF(ISERROR(COUNTIF('\\ad1.prod\hig\Shared\Continu ous Performance
Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS
Compltd.xls]RIS
Completed'!$B:$B,D12)),"OpnRISfile",IF(COUNTIF('\\ ad1.prod\hig\Shared\Continuous
Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS
Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)0,"Yes","No"))

Local open:
'=IF(ISERROR(COUNTIF('S:\Shared\Continuous Performance Improvement\CE Site
Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS
Completed'!$B:$B,D15)),"OpnRISfile",IF(COUNTIF('S: \Shared\Continuous
Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS
Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)0,"Yes","No"))

--

Dave Peterson