Just to add to Glenn's response:
=sumif(indirect("'["&$d$6&".xlsx]weekly timesheet'!l2:l19"),c6,
indirect("'["&$d$6&".xlsx]weekly timesheet'!j2:j19"))
(I didn't test it, so watch out for my typos!)
Since L2:L19 and J2:J19 are within doublequotes (and are strings, the $'s
weren't necessary.
If you still have trouble, share what's in D6, too. It should be just the name
(without the extension and without the drive and path).
Nancy Taylor wrote:
Hi Dave,
I haven't used the Indirect function before so I've been playing with it to
see how it works. I've been able to get the SUMIF statement built but I am
getting that annoying #REF! error. I do have the workbook that I am
referencing open and I have used the debug to step into the formula to make
sure everything is resolving correctly. Looks OK but I can't get it to work.
In case you can spot something easy that I can fix, here's my new formula:
=INDIRECT("SUMIF('["&$D$6&".xlsx]Weekly
Timesheet'!$L$2:$L$19,"&C6&",'["&$D$6&".xlsx]Weekly Timesheet'!$J$2:$J$19)")
I'll keep trying...
Thanks for the tip!
Nancy
"Dave Peterson" wrote:
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 can€„˘t seem to get that to work.
Any thoughts?
--
Dave Peterson
--
Dave Peterson