Leslie,
It references C1 on the same sheet as the formula. Make sure that C1 has
exactly the same value as one of your month name cells: use copy and paste
values to do that.
Also, you may have had weird line wraps introduced by your newsreader
program, so take out any extra line breaks.
If you can't get it to work, I will send you two workbooks that have a
working example. Reply privately, and take out the spaces and change the
dot to . in my email address.
HTH,
Bernie
MS Excel MVP
"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Thanks again very much. Hopefully one last question... In the formula
> does
> C1 reference the worksheet in the destination workbook or in the original
> workbook? The reason I am asking is because I entered the formula in my
> new
> worksheet and I am getting an error. It is looking at the Cell C1 in the
> new
> worksheet not the original. Thanks again I so much appreciate it as I've
> been racking my brain for days.
>
> "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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>
Bookmarks