Sumproduct I think but a tough one
 

Source Sheet

D8:Q8 contains data -1 through 12 which represents months of the current
year (1 thru 12)and the 2 from the previous year (-1, and 0)

Range B10: b129 contains account numbers

Range d10:d129 contains costing data

Summary Sheet

Cell b3 will contain either -1 through 12 based on user request
Row J will contain the account # (j1 =100, J2=200, etc..)
The rest of the document requires no other intervention as it is preset with
values.


I need a formula that will lookup cell B3 and J6 in the summary sheet and
look to the source sheet and show me the costing

Hope that makes sense


Thanks alot

Sumproduct I think but a tough one
 

Range d10:d129 contains costing data

Is that supposed to be:

Range D10:Q129 contains costing data

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
Source Sheet

D8:Q8 contains data -1 through 12 which represents months of the current
year (1 thru 12)and the 2 from the previous year (-1, and 0)

Range B10: b129 contains account numbers

Range d10:d129 contains costing data

Summary Sheet

Cell b3 will contain either -1 through 12 based on user request
Row J will contain the account # (j1 =100, J2=200, etc..)
The rest of the document requires no other intervention as it is preset
with
values.


I need a formula that will lookup cell B3 and J6 in the summary sheet and
look to the source sheet and show me the costing

Hope that makes sense


Thanks alot

Sumproduct I think but a tough one
 

Yes

Sorry and Thanks

"T. Valko" wrote:

Range d10:d129 contains costing data

Is that supposed to be:

Range D10:Q129 contains costing data

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
Source Sheet

D8:Q8 contains data -1 through 12 which represents months of the current
year (1 thru 12)and the 2 from the previous year (-1, and 0)

Range B10: b129 contains account numbers

Range d10:d129 contains costing data

Summary Sheet

Cell b3 will contain either -1 through 12 based on user request
Row J will contain the account # (j1 =100, J2=200, etc..)
The rest of the document requires no other intervention as it is preset
with
values.


I need a formula that will lookup cell B3 and J6 in the summary sheet and
look to the source sheet and show me the costing

Hope that makes sense


Thanks alot


.

Sumproduct I think but a tough one
 

Are B10:B129 all different? If so you could use

=INDEX('Source'!D$10:Q$129,MATCH(J1,'Source'!B$10: B$129,0),MATCH(B
$3,'Source'!D$8:Q$8,0))

regards, barry

or if there might be multiple matches in column B

=SUMIF('Source'!B$10:B$129,J1,INDEX('Source'!D$10: Q$129,0,MATCH(B
$3,'Source'!D$8:Q$8,0)))

regards, barry

Sumproduct I think but a tough one
 

Thx alot

"barry houdini" wrote:

Are B10:B129 all different? If so you could use

=INDEX('Source'!D$10:Q$129,MATCH(J1,'Source'!B$10: B$129,0),MATCH(B
$3,'Source'!D$8:Q$8,0))

regards, barry

or if there might be multiple matches in column B

=SUMIF('Source'!B$10:B$129,J1,INDEX('Source'!D$10: Q$129,0,MATCH(B
$3,'Source'!D$8:Q$8,0)))

regards, barry
.