You've got a couple of obstacles to overcome.

First...

What you'd want to use is =indirect() and build a formula that results in the
string that points at the folder, file, sheet, location.

But the bad thing is that =indirect() won't work if that sending file is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

Second...
=sumif() will return an error if the workbook is closed.

You could replace it with =sumproduct()

=sumproduct(--('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22=C6),
('[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22))

But you'd still may have to overcome the closed workbook problem.

ps. You can't use the entire column in the =sumproduct() formula unless you're
using xl2007.

Nancy Taylor wrote:

I hope this is an easy question to answer...

I am attempting to use a SUMIF to pull in numbers from a separate timesheet
and I would like to build part of the external reference workbook name from a
cell instead of hard-coding it. For example:

=SUMIF('[FirstName_LastName.xlsx]Sheet'!$L$5:$L$22,C6,'[FirstName_LastName.xlsx]Sheet'!$K$5:$K$22)

I have a cell with the resource name and I would like to plug in the name
from that cell for €œFirstName_LastName€� but I cant seem to get that to work.


Any thoughts?

--

Dave Peterson