Building Dynamic SUMIF statement

[Nancy Taylor]

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]

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.

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?

 

[Nancy Taylor]

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

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.

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?

 

[Glenn]

Put the INDIRECT inside the SUMIF, not the other way around.

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

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.

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]

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).

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

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.

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?

 

[Nancy Taylor]

Beautiful! Thank you so much for your help! I appreciate it soooo much!

I can call it a day now and enjoy my weekend without this pesky problem
keeping me up all night!

Hope you have a wonderful weekend!
Nancy

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).

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

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?

 

[Nancy Taylor]

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

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.

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?

 

[Don Guillett]

Indirect does NOT work with closed workbooks.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext,
but
my formulas still give me a #REF! if the associated spreadsheets are
closed.
Is there a trick to getting the indirect.ext to work?

Nancy

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.

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]

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

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.

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?

 

[Nancy Taylor]

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

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?

 

[Don Guillett]

As Dave and I both said, INDIRECT does NOT work with closed workbooks. !!!

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the
simple
=indirect.ext() working first.) And share the values in each of the
cells that
the formula refers to.

I'm sure you'll get some suggestions.

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the
indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are
closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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]

Nancy is using =indirect.ext() from Laurent Longre's addin (morefunc.xll).

As Dave and I both said, INDIRECT does NOT work with closed workbooks. !!!

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the
simple
=indirect.ext() working first.) And share the values in each of the
cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the
indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are
closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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]

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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]

ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too.

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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?

 

[Nancy Taylor]

Hi Dave,

thanks for the update. A couple of comments...
- I will deifnitely have everyone using these spreadsheets install the add
in - no problem.
- The detail spreadsheets will be in the same folder as the summary
spreadsheet. Since I don't dictate where these go, I was expecting to not
have to specify drives, etc if they will reside together. True?
- all users have xl2007 so I'm ok there.
- the date format is really a text field that is part of the filename. I
don't think I need to handle any formatting there.

I am not seeing the benefit of the indirect.ext at this point though.
Unless I open the detail spreadsheets, the indirect.ext doesn't seem to
resolve the references in the closed spreadsheets and shows only #REF!. I
have to open the detail files to get the reference to resolve itself still??

ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too.

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?
:

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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?

 

[Nancy Taylor]

I believe I may have found information that explains what I am seeing. I
found a posting that says the indirect.ext only works for a single cell
reference. Since I am trying to use it to retrieve a range of cells from a
closed workbook, I don't think it will work for me?

Hi Dave,

thanks for the update. A couple of comments...
- I will deifnitely have everyone using these spreadsheets install the add
in - no problem.
- The detail spreadsheets will be in the same folder as the summary
spreadsheet. Since I don't dictate where these go, I was expecting to not
have to specify drives, etc if they will reside together. True?
- all users have xl2007 so I'm ok there.
- the date format is really a text field that is part of the filename. I
don't think I need to handle any formatting there.

I am not seeing the benefit of the indirect.ext at this point though.
Unless I open the detail spreadsheets, the indirect.ext doesn't seem to
resolve the references in the closed spreadsheets and shows only #REF!. I
have to open the detail files to get the reference to resolve itself still??

ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too.

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Nancy Taylor wrote:

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?
:

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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]

First, I don't use Laurent's addin very often (hardly ever).

But it sure makes sense that you'll have to specify the drive and folder for it
to work (if those "sending" workbooks are closed).

And I bet if you try your formula with the drive/folder specified, you'll find
that your =sumproduct() works ok.

The problem I've had with using entire columns (in xl2003!) is that I sometimes
get the message "unable to complete the task with available resources" (or
something like that). I have to adjust the ranges to make my non-indirect
formulas work.

I believe I may have found information that explains what I am seeing. I
found a posting that says the indirect.ext only works for a single cell
reference. Since I am trying to use it to retrieve a range of cells from a
closed workbook, I don't think it will work for me?

Hi Dave,

thanks for the update. A couple of comments...
- I will deifnitely have everyone using these spreadsheets install the add
in - no problem.
- The detail spreadsheets will be in the same folder as the summary
spreadsheet. Since I don't dictate where these go, I was expecting to not
have to specify drives, etc if they will reside together. True?
- all users have xl2007 so I'm ok there.
- the date format is really a text field that is part of the filename. I
don't think I need to handle any formatting there.

I am not seeing the benefit of the indirect.ext at this point though.
Unless I open the detail spreadsheets, the indirect.ext doesn't seem to
resolve the references in the closed spreadsheets and shows only #REF!. I
have to open the detail files to get the reference to resolve itself still??

ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too.

Dave Peterson wrote:

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Nancy Taylor wrote:

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?
:

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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?

 

[Nancy Taylor]

Well, I included the full path to the file but still no dice:
=SUMPRODUCT(--(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

I tried with and without the "\". I'm stumped...

Hi Dave,

thanks for the update. A couple of comments...
- I will deifnitely have everyone using these spreadsheets install the add
in - no problem.
- The detail spreadsheets will be in the same folder as the summary
spreadsheet. Since I don't dictate where these go, I was expecting to not
have to specify drives, etc if they will reside together. True?
- all users have xl2007 so I'm ok there.
- the date format is really a text field that is part of the filename. I
don't think I need to handle any formatting there.

I am not seeing the benefit of the indirect.ext at this point though.
Unless I open the detail spreadsheets, the indirect.ext doesn't seem to
resolve the references in the closed spreadsheets and shows only #REF!. I
have to open the detail files to get the reference to resolve itself still??

ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too.

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Nancy Taylor wrote:

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?
:

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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]

If you convert your cell's to their values and drop the =indirect.ext(), does it
work?

=SUMPRODUCT(
--(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]Weekly Timesheet'!$N:$N")=C6),
(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]weekly timesheet'!$L:$L")))

would become:

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

=SUMPRODUCT(
--('c:\temp\[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!$N:$N=C6),
'c:\temp\[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!$L:$L)

Does it work?

If you shorten the ranges:

=SUMPRODUCT(
--('c:\temp\[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!$N1:$N11=C6),
'c:\temp\[09122009_Nancy Taylor.xlsm]Weekly Timesheet'!$L1:$L11)

Does it work?

I'm still guessing that you've got a typo/syntax problem somewhere or your
string needs to be created slightly differently.

Well, I included the full path to the file but still no dice:
=SUMPRODUCT(--(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'c:\temp\["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

I tried with and without the "\". I'm stumped...

Hi Dave,

thanks for the update. A couple of comments...
- I will deifnitely have everyone using these spreadsheets install the add
in - no problem.
- The detail spreadsheets will be in the same folder as the summary
spreadsheet. Since I don't dictate where these go, I was expecting to not
have to specify drives, etc if they will reside together. True?
- all users have xl2007 so I'm ok there.
- the date format is really a text field that is part of the filename. I
don't think I need to handle any formatting there.

I am not seeing the benefit of the indirect.ext at this point though.
Unless I open the detail spreadsheets, the indirect.ext doesn't seem to
resolve the references in the closed spreadsheets and shows only #REF!. I
have to open the detail files to get the reference to resolve itself still??

ppps. Remember that if you do share the workbook with that formula with others,
then the recipients will have to have that MoreFunc.xll addin, too.

Dave Peterson wrote:

You need to include the drive and folder, too. Otherwise, how would
=indirect.ext() know which drive/folder to look into.

ps. If that 09122009 is a date that's formatted as mmddyyyy (or ddmmyyyy),
you'll want to use:

...&text($b$2,"mmddyyyy")&...

or if it's a plain old number that's formatted to show leading 0's:

...&text($b$2,"00000000")&...

========
pps. Remember that if you have to share this workbook with people not using
xl2007, then you won't want to use the entire column. (that only works in
xl2007.)

Nancy Taylor wrote:

Not sure if my last post worked so trying again...

Here's my formula:

=SUMPRODUCT(--(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]Weekly
Timesheet'!$N:$N")=C6),(INDIRECT.EXT("'["&$B$2&"_"&D6&".xlsm]weekly
timesheet'!$L:$L")))

B2 = 09122009
D6 = Nancy Taylor
C6 = Project and phase name I need to total
column N = timesheet project and phase names
column L = timesheet hours

It seems to work if I have the detail spreadsheets open before I open the
summary spreadsheet but if the detail spreadsheets are closed, the #REF!
comes up. If I then open the detail sheets, the #REF! resolves itself.

Make any sense to anyone?
:

For me, it's more trial and error.

If you can't get it working, share the formula you used. (I'd get the simple
=indirect.ext() working first.) And share the values in each of the cells that
the formula refers to.

I'm sure you'll get some suggestions.

Nancy Taylor wrote:

Hi Dave,

Well, I have gone through and replaced my sumifs with sumproducts and
downloaded the morefunc so that I can take advantage of the indirect.ext, but
my formulas still give me a #REF! if the associated spreadsheets are closed.
Is there a trick to getting the indirect.ext to work?

Nancy

:

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?