dynamic, double vlookup, match, index, dget?? different workbooks

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

Then it should pickup the value in column G, which is 6 columns from
A1:A1000 - unless you changed that part of the formula.

OFFSET('[Book Name.xls]Sheet Name'!$A$1:$A$1000 < If you changed this
column, you need to change the -1 part.

Try

MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-COLUMN(range that you
changed the first part to)))

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
>I believe 6.
>
> "Bernie Deitrick" wrote:
>
>> Leslie,
>>
>> What does this formula return?
>>
>> =MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Leslie" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[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.
>> >> >
>> >> >
>> >> >
>>
>>
>>

I believe 6.

"Bernie Deitrick" wrote:

> Leslie,
>
> What does this formula return?
>
> =MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[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.
> >> >
> >> >
> >> >
>
>
>

Leslie,

What does this formula return?

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

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[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.
>> >
>> >
>> >

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" <[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.
> >
> >
> >

I emailed you but you may need to look under Spam because i used a different
email address.

"Bernie Deitrick" wrote:

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

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" <[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.
>
>
>

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

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

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.

"Bernie Deitrick" wrote:

> Leslie,
>
> Assuming your data table starts in cell A1 of Book Name.xls, sheet "Sheet
> Name", with labels across the first row. The compay name you want to look
> up is in cell A1, the FY is in cell A2, and the month is in cell A3. Note
> that the FY needs to have "FY 2004" not just 2004, to be an exact match of
> your data. And your month in cell A3 need to be Jly. etc.
>
> =SUMPRODUCT(('[Book Name.xls]Sheet name'!A1:A1000=A1)*('[Book Name.xls]Sheet
> name'!B1:B1000=A2),OFFSET('[Book Name.xls]Sheet
> name'!A1:A1000,0,MATCH(A3,'[Book Name.xls]Sheet name'!1:1,FALSE)-1))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > Below is sample data. I need to be able to lookup a company (company A,
> for
> > example) for fiscal year 2004 and in another workbook I want July's figure
> to
> > populate the field in the other workbook. The formula also has to take
> into
> > account that on this worksheet you see here, I will be inserting and
> possibly
> > deleting rows at any time. Therefore, the formula needs to be such that if
> I
> > do that, the cell which is populated in the other workbook is still
> correct.
> > Thanks for any help.
> >
> > Company FY Jly. Aug. Sep.
> > A FY 2005 $6,502.20 $9,899.43 $28,916.10
> > A FY 2004 $6,949.45 $4,065.00 $5,285.64
> > B FY 2005 $8,116.75 $2,195.40 $747.40
> > B FY 2004 $2,166.40 $3,816.34 $814.34
> > C FY 2005 $283.00 $195.00 $1,716.40
> > C FY 2004 $404.20 $2,658.33 $982.40
> >
>
>
>

Leslie,

Assuming your data table starts in cell A1 of Book Name.xls, sheet "Sheet
Name", with labels across the first row. The compay name you want to look
up is in cell A1, the FY is in cell A2, and the month is in cell A3. Note
that the FY needs to have "FY 2004" not just 2004, to be an exact match of
your data. And your month in cell A3 need to be Jly. etc.

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

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Below is sample data. I need to be able to lookup a company (company A,
for
> example) for fiscal year 2004 and in another workbook I want July's figure
to
> populate the field in the other workbook. The formula also has to take
into
> account that on this worksheet you see here, I will be inserting and
possibly
> deleting rows at any time. Therefore, the formula needs to be such that if
I
> do that, the cell which is populated in the other workbook is still
correct.
> Thanks for any help.
>
> Company FY Jly. Aug. Sep.
> A FY 2005 $6,502.20 $9,899.43 $28,916.10
> A FY 2004 $6,949.45 $4,065.00 $5,285.64
> B FY 2005 $8,116.75 $2,195.40 $747.40
> B FY 2004 $2,166.40 $3,816.34 $814.34
> C FY 2005 $283.00 $195.00 $1,716.40
> C FY 2004 $404.20 $2,658.33 $982.40
>

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" <[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.
> >
> >
> >

Leslie,

What does this formula return?

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

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[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.
>> >
>> >
>> >

I emailed you but you may need to look under Spam because i used a different
email address.

"Bernie Deitrick" wrote:

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

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

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" <[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.
>
>
>

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

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

I believe 6.

"Bernie Deitrick" wrote:

> Leslie,
>
> What does this formula return?
>
> =MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[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.
> >> >
> >> >
> >> >
>
>
>

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.

"Bernie Deitrick" wrote:

> Leslie,
>
> Assuming your data table starts in cell A1 of Book Name.xls, sheet "Sheet
> Name", with labels across the first row. The compay name you want to look
> up is in cell A1, the FY is in cell A2, and the month is in cell A3. Note
> that the FY needs to have "FY 2004" not just 2004, to be an exact match of
> your data. And your month in cell A3 need to be Jly. etc.
>
> =SUMPRODUCT(('[Book Name.xls]Sheet name'!A1:A1000=A1)*('[Book Name.xls]Sheet
> name'!B1:B1000=A2),OFFSET('[Book Name.xls]Sheet
> name'!A1:A1000,0,MATCH(A3,'[Book Name.xls]Sheet name'!1:1,FALSE)-1))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > Below is sample data. I need to be able to lookup a company (company A,
> for
> > example) for fiscal year 2004 and in another workbook I want July's figure
> to
> > populate the field in the other workbook. The formula also has to take
> into
> > account that on this worksheet you see here, I will be inserting and
> possibly
> > deleting rows at any time. Therefore, the formula needs to be such that if
> I
> > do that, the cell which is populated in the other workbook is still
> correct.
> > Thanks for any help.
> >
> > Company FY Jly. Aug. Sep.
> > A FY 2005 $6,502.20 $9,899.43 $28,916.10
> > A FY 2004 $6,949.45 $4,065.00 $5,285.64
> > B FY 2005 $8,116.75 $2,195.40 $747.40
> > B FY 2004 $2,166.40 $3,816.34 $814.34
> > C FY 2005 $283.00 $195.00 $1,716.40
> > C FY 2004 $404.20 $2,658.33 $982.40
> >
>
>
>

Leslie,

Assuming your data table starts in cell A1 of Book Name.xls, sheet "Sheet
Name", with labels across the first row. The compay name you want to look
up is in cell A1, the FY is in cell A2, and the month is in cell A3. Note
that the FY needs to have "FY 2004" not just 2004, to be an exact match of
your data. And your month in cell A3 need to be Jly. etc.

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

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Below is sample data. I need to be able to lookup a company (company A,
for
> example) for fiscal year 2004 and in another workbook I want July's figure
to
> populate the field in the other workbook. The formula also has to take
into
> account that on this worksheet you see here, I will be inserting and
possibly
> deleting rows at any time. Therefore, the formula needs to be such that if
I
> do that, the cell which is populated in the other workbook is still
correct.
> Thanks for any help.
>
> Company FY Jly. Aug. Sep.
> A FY 2005 $6,502.20 $9,899.43 $28,916.10
> A FY 2004 $6,949.45 $4,065.00 $5,285.64
> B FY 2005 $8,116.75 $2,195.40 $747.40
> B FY 2004 $2,166.40 $3,816.34 $814.34
> C FY 2005 $283.00 $195.00 $1,716.40
> C FY 2004 $404.20 $2,658.33 $982.40
>

Then it should pickup the value in column G, which is 6 columns from
A1:A1000 - unless you changed that part of the formula.

OFFSET('[Book Name.xls]Sheet Name'!$A$1:$A$1000 < If you changed this
column, you need to change the -1 part.

Try

MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-COLUMN(range that you
changed the first part to)))

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
>I believe 6.
>
> "Bernie Deitrick" wrote:
>
>> Leslie,
>>
>> What does this formula return?
>>
>> =MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Leslie" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[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.
>> >> >
>> >> >
>> >> >
>>
>>
>>

Leslie,

What does this formula return?

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

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[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.
>> >
>> >
>> >

Then it should pickup the value in column G, which is 6 columns from
A1:A1000 - unless you changed that part of the formula.

OFFSET('[Book Name.xls]Sheet Name'!$A$1:$A$1000 < If you changed this
column, you need to change the -1 part.

Try

MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-COLUMN(range that you
changed the first part to)))

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
>I believe 6.
>
> "Bernie Deitrick" wrote:
>
>> Leslie,
>>
>> What does this formula return?
>>
>> =MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Leslie" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[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.
>> >> >
>> >> >
>> >> >
>>
>>
>>

I believe 6.

"Bernie Deitrick" wrote:

> Leslie,
>
> What does this formula return?
>
> =MATCH($C$1,'[Book Name.xls]Sheet Name'!$1:$1,FALSE)-1
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[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.
> >> >
> >> >
> >> >
>
>
>

Below is sample data. I need to be able to lookup a company (company A, for
example) for fiscal year 2004 and in another workbook I want July's figure to
populate the field in the other workbook. The formula also has to take into
account that on this worksheet you see here, I will be inserting and possibly
deleting rows at any time. Therefore, the formula needs to be such that if I
do that, the cell which is populated in the other workbook is still correct.
Thanks for any help.

Company FY Jly. Aug. Sep.
A FY 2005 $6,502.20 $9,899.43 $28,916.10
A FY 2004 $6,949.45 $4,065.00 $5,285.64
B FY 2005 $8,116.75 $2,195.40 $747.40
B FY 2004 $2,166.40 $3,816.34 $814.34
C FY 2005 $283.00 $195.00 $1,716.40
C FY 2004 $404.20 $2,658.33 $982.40

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" <[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.
> >
> >
> >

I emailed you but you may need to look under Spam because i used a different
email address.

"Bernie Deitrick" wrote:

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

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

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" <[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.
>
>
>

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

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

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.

"Bernie Deitrick" wrote:

> Leslie,
>
> Assuming your data table starts in cell A1 of Book Name.xls, sheet "Sheet
> Name", with labels across the first row. The compay name you want to look
> up is in cell A1, the FY is in cell A2, and the month is in cell A3. Note
> that the FY needs to have "FY 2004" not just 2004, to be an exact match of
> your data. And your month in cell A3 need to be Jly. etc.
>
> =SUMPRODUCT(('[Book Name.xls]Sheet name'!A1:A1000=A1)*('[Book Name.xls]Sheet
> name'!B1:B1000=A2),OFFSET('[Book Name.xls]Sheet
> name'!A1:A1000,0,MATCH(A3,'[Book Name.xls]Sheet name'!1:1,FALSE)-1))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Leslie" <[email protected]> wrote in message
> news:[email protected]...
> > Below is sample data. I need to be able to lookup a company (company A,
> for
> > example) for fiscal year 2004 and in another workbook I want July's figure
> to
> > populate the field in the other workbook. The formula also has to take
> into
> > account that on this worksheet you see here, I will be inserting and
> possibly
> > deleting rows at any time. Therefore, the formula needs to be such that if
> I
> > do that, the cell which is populated in the other workbook is still
> correct.
> > Thanks for any help.
> >
> > Company FY Jly. Aug. Sep.
> > A FY 2005 $6,502.20 $9,899.43 $28,916.10
> > A FY 2004 $6,949.45 $4,065.00 $5,285.64
> > B FY 2005 $8,116.75 $2,195.40 $747.40
> > B FY 2004 $2,166.40 $3,816.34 $814.34
> > C FY 2005 $283.00 $195.00 $1,716.40
> > C FY 2004 $404.20 $2,658.33 $982.40
> >
>
>
>

Leslie,

Assuming your data table starts in cell A1 of Book Name.xls, sheet "Sheet
Name", with labels across the first row. The compay name you want to look
up is in cell A1, the FY is in cell A2, and the month is in cell A3. Note
that the FY needs to have "FY 2004" not just 2004, to be an exact match of
your data. And your month in cell A3 need to be Jly. etc.

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

HTH,
Bernie
MS Excel MVP


"Leslie" <[email protected]> wrote in message
news:[email protected]...
> Below is sample data. I need to be able to lookup a company (company A,
for
> example) for fiscal year 2004 and in another workbook I want July's figure
to
> populate the field in the other workbook. The formula also has to take
into
> account that on this worksheet you see here, I will be inserting and
possibly
> deleting rows at any time. Therefore, the formula needs to be such that if
I
> do that, the cell which is populated in the other workbook is still
correct.
> Thanks for any help.
>
> Company FY Jly. Aug. Sep.
> A FY 2005 $6,502.20 $9,899.43 $28,916.10
> A FY 2004 $6,949.45 $4,065.00 $5,285.64
> B FY 2005 $8,116.75 $2,195.40 $747.40
> B FY 2004 $2,166.40 $3,816.34 $814.34
> C FY 2005 $283.00 $195.00 $1,716.40
> C FY 2004 $404.20 $2,658.33 $982.40
>