Match?,Index?, Sum? or some other way?
 

Currently have this formula which works great.
{=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!C$3,DETAIL!E$3:$E$9609,0),0))}

But that only works if my numbers I am pulling from are always on Detail in
column E3:E9609

The twist is I would like for the formula to also look at Cell 'YTD-BR'!$C1
, where the month name Jan will be entered and then go to worksheet Detail
and find the column that has Jan heading. (Jan - Dec will be in Cells E1 thu
P1 on Detail) and return the numbers in that months column to the YTD-BR
worksheet.

I am lost at this point and would appreciate help. Thanks in Advance.

Match?,Index?, Sum? or some other way?
 

Use the offset function.

=SUM(OFFSET(Detail!$E$3,0,MATCH('YTD-BR'!$C1 ,Detail!E1:P1,0)-1,9607,1))

Note that the 9607 near the end needs to be equal to number of rows of data
that you want to look in.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sue" wrote:

Currently have this formula which works great.
{=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!C$3,DETAIL!E$3:$E$9609,0),0))}

But that only works if my numbers I am pulling from are always on Detail in
column E3:E9609

The twist is I would like for the formula to also look at Cell 'YTD-BR'!$C1
, where the month name Jan will be entered and then go to worksheet Detail
and find the column that has Jan heading. (Jan - Dec will be in Cells E1 thu
P1 on Detail) and return the numbers in that months column to the YTD-BR
worksheet.

I am lost at this point and would appreciate help. Thanks in Advance.

Match?,Index?, Sum? or some other way?
 

Try this:

Normally entered...

=SUMPRODUCT(--(Detail!$B$3:$B$9609='YTD-BR'!$A6),--(Detail!$C$3:$C$9609='YTD-BR'!C$3),INDEX(Detail!$E$3:$P$9609,,MATCH('YTD-BR'!$C1,Detail!$E$1:$P$1,0)))


--
Biff
Microsoft Excel MVP


"Sue" wrote in message
...
Currently have this formula which works great.
{=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!C$3,DETAIL!E$3:$E$9609,0),0))}

But that only works if my numbers I am pulling from are always on Detail
in
column E3:E9609

The twist is I would like for the formula to also look at Cell
'YTD-BR'!$C1
, where the month name Jan will be entered and then go to worksheet
Detail
and find the column that has Jan heading. (Jan - Dec will be in Cells E1
thu
P1 on Detail) and return the numbers in that months column to the YTD-BR
worksheet.

I am lost at this point and would appreciate help. Thanks in Advance.

Match?,Index?, Sum? or some other way?
 

"Luke M" wrote:

Use the offset function.

=SUM(OFFSET(Detail!$E$3,0,MATCH('YTD-BR'!$C1 ,Detail!E1:P1,0)-1,9607,1))

Note that the 9607 near the end needs to be equal to number of rows of data
that you want to look in.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sue" wrote:

Currently have this formula which works great.
{=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!C$3,DETAIL!E$3:$E$9609,0),0))}

But that only works if my numbers I am pulling from are always on Detail in
column E3:E9609

The twist is I would like for the formula to also look at Cell 'YTD-BR'!$C1
, where the month name Jan will be entered and then go to worksheet Detail
and find the column that has Jan heading. (Jan - Dec will be in Cells E1 thu
P1 on Detail) and return the numbers in that months column to the YTD-BR
worksheet.

I am lost at this point and would appreciate help. Thanks in Advance.

Unfortunately this returned the total for the entire column and did not
bring in the individual cells

Match?,Index?, Sum? or some other way?
 

Try...

=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!
C$3,INDEX(DETAIL!E$3:$P$9609,0,MATCH('YTD-BR'!$C1,DETAIL!E$1:$P$1,0)))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

http://www.xl-central.com

In article ,
Sue wrote:

"Luke M" wrote:

Use the offset function.

=SUM(OFFSET(Detail!$E$3,0,MATCH('YTD-BR'!$C1 ,Detail!E1:P1,0)-1,9607,1))

Note that the 9607 near the end needs to be equal to number of rows of data
that you want to look in.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sue" wrote:

Currently have this formula which works great.
{=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!
C$3,DETAIL!E$3:$E$9609,0),0))}

But that only works if my numbers I am pulling from are always on Detail
in
column E3:E9609

The twist is I would like for the formula to also look at Cell
'YTD-BR'!$C1
, where the month name Jan will be entered and then go to worksheet
Detail
and find the column that has Jan heading. (Jan - Dec will be in Cells E1
thu
P1 on Detail) and return the numbers in that months column to the YTD-BR
worksheet.

I am lost at this point and would appreciate help. Thanks in Advance.

Unfortunately this returned the total for the entire column and did not
bring in the individual cells