Leslie,

What does this formula return?

=MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1

HTH,
Bernie
MS Excel MVP


"Leslie" wrote in message
...
Dear Bernie:

I have the correct formula but for some reason it is pulling in a number
from the same row but a different month in column K. I have double and
triple checked my formula. Does it have something to do with the OFFSET
or
MATCH portion of this formula? I can't figure it out. Does It have
something to do with the last portion (i.e. !$1:$1,False)-1)) Thank you in
advance.

=SUMPRODUCT(('[Book Name.xls]Sheet Name'!$A$1:$A$1000=$A$2)*('[Book
Name.xls]Sheet Name'!$B$1:$B$1000=$B$2),OFFSET('[Book Name.xls]Sheet
Name'!$A$1:$A$1000,0,MATCH($C$1,'[Book Name.xls]Sheet
Name'!$1:$1,FALSE)-1))


"Leslie" wrote:

Sorry, did you see this part of my earlier question?

Another question, The reason I need
this formula is because I'm creating other workbooks which will each
be various
subsets of this worksheet. How does the other worksheet know to pull
for
company A, FY 2004 in July instead of company C, FY 2005 for July? I
can't tell from the formula

"Bernie Deitrick" wrote:

If the labels are in the first row, instead of A1,
A2 and A3 don't you mean A1, B1, C1.

No, not really. Those are the cells that contain the key values that
you
want to extract from your database, and are on the same sheet as your
formula. They _could_ be in A1, B1, and C1, so you would change the
formula
to

=SUMPRODUCT(('[Book Name.xls]Sheet name'!A1:A1000=A1)*('[Book
Name.xls]Sheet
name'!B1:B1000=B1),OFFSET('[Book Name.xls]Sheet
name'!A1:A1000,0,MATCH(C1,'[Book Name.xls]Sheet name'!1:1,FALSE)-1))

This part ensures that only the correct company is pulled:

('[Book Name.xls]Sheet name'!A1:A1000=A1)

This part ensure that only the correct FY is pulled

('[Book Name.xls]Sheet name'!B1:B1000=B1)

and this part finds the correct month:

MATCH(C1,'[Book Name.xls]Sheet name'!1:1,FALSE)

and this part is pulls in the actual data:

OFFSET('[Book Name.xls]Sheet name'!A1:A1000,0,MATCH(C1,'[Book
Name.xls]Sheet
name'!1:1,FALSE)-1))

It's a complex formula, but you have a complex problem.

If you don't want to use a formula, you could use a pivot table, but
then
you would need to use a database rather than a cross-tab table, which
would
look like:

Company FY Month Amt.
A FY 2005 Jly. $6,502.20
A FY 2005 Aug. $9,899.43
A FY 2005 Sep. $28,916.10
etc....

HTH,
Bernie
MS Excel MVP


"Leslie" wrote in message
...
Thank you for your reply. If the labels are in the first row,
instead of
A1,
A2 and A3 don't you mean A1, B1, C1. I'm new using these functions
so any
further explanation is appreciated. Another question, The reason I
need
this
formula is because I'm creating other workbooks which will each be
various
subsets of this worksheet. How does the other worksheet know to pull
for
company A, FY 2004 in July instead of company C, FY 2005 for July? I
can't
tell from the formula. Thanks again.