Saved from a previous post:
There are functions that don't work when the "sending" workbook is closed.
=sumif(), =countif() and =indirect() are a few.
But there are workarounds:
=sumproduct(--('[yourfilename]Fargo'!$B$10:$B$134="April-09"))
If those cells were really dates:
=sumproduct(--(text('[yourfilename]Fargo'!$B$10:$B$134,"yyyymm")="200904"))
Build the formula with your workbooks open and let excel worry about the syntax
when you close the sending workbook.
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
JICDB wrote:
I have a similair issue and had a question. I am not using INDEX/MATCH but
SUMIF instead. Will this work with this expression as well? I have a
spreadsheet using SUMIFs to summarize data from 6 separate workbooks and
every time I open the main document I have to open each workbook to make the
connection. Even if I go through the update links portion I can't update any
information without opening all 6 files. If it doesn't work with SUMIF I
could create a sum worksheet on each of the 6 and use INDEX/Match to get the
data. Thanks!
"smartin" wrote:
Lady Success wrote:
I have two worksheets - one with a database and one that does a lookup on the
database. How can I pull information from the database based on the
information entered in the lookup worksheet?
My Lookup worksheet contains two fields that need data entry by the end user.
B2 User enters Jcode
B3 User enters drug name
B4 Populates information based on what's found in the database
My current formula in the lookup file is:
(INDEX('2010 HBR Query Tool Only.xlsx'!DrugName,
MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0).
The formula above is currently using the database that's contained in the
same workbook as the lookup. If I want to use another file, I assume that I
would just change the name of the file to the name of the external file.
However, does Excel require the external file to be open in order to perform
its operation? The contents of the database are used for different
workbooks and different functions as well as different users in different
depts with different access. I want to have one version of the database so
I won't have to update more than one file with changes. If the file has to
be open in order for the formulas to work, is there a way that I can code the
lookup document so that it will open the database automatically (behind the
scenes for the most people) and then close when the lookup file is closed?
I don't know if this is even possible or not - so if anyone can help me with
this I would appreciate it.
i.e. (I posted this question earlier - but the posting didn't stay in the
forum? So, if you find this is a duplicate, I apologize.)
Yes, it's possible, and no, the other workbook does not have to be open
for this to work. However, when setting up the formulas it is easier to
get the syntax correct with the other workbook open.
Assuming you have the external book open, basically all you have to do
is start entering your formula and when you need to refer to the other
workbook simply switch windows, point to the range you need (or perhaps
call up the named range picker in your case) in the other workbook,
switch back, and continue.
Once this is done, the external book need not be opened again. When
someone opens the lookup file they will probably be prompted to refresh
the external link values, which is the normal thing to do. Excel will
call up the needed values and refresh the lookup file without actually
opening the other book.
--
Dave Peterson