Advanced Lookups

One play to try ..

Assume the monthly sheets
are named as: Jan, Feb, etc
with tables in cols A to C, data from row2 down, viz:

> Cust P/N Qty
> a 1-1 4
> a 1-2 3
> b 1-1 8
> b 1-2 7

In your new sheet layout below,
let's revise the headers for cols C, D (in C1, D1, ... across)
from: "Jan Qty", "Feb Qty", etc
to just: Jan, Feb, etc (consistent with the actual sheetnames)

(Above will simplify it for us to use INDIRECT to read the col headers)

> Cust P/N Jan Qty Feb Qty
> a 1-1
> a 1-2
> b 1-1
> b 1-2

Put in C2, and array-enter (press CTRL+SHIFT+ENTER):

=INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A
2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0))

Copy C2 across and fill down to populate the table

Adapt the ranges: A2:A100, C2:C100, etc to suit

And perhaps better with an error trap to return blanks: "" instead of errors
for a much cleaner looking output, we could put instead in C2, and
array-enter:

=IF(ISERROR(MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"
&C$1&"'!B2:B100"),0)),"",INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$
B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0)))

Then just copy C2 across and fill down as before to populate the table
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steve"
<[email protected]@discussions.microsoft.com>
wrote in message news:[email protected]...
> I'm not too sure if this would be a formula or VB thing. I am making a
> Yearly sales report/forecast. What I need to do is look up each part
number
> and pull the qty from each but only for specific customer (EX: Qty for p/n
> 1-1 but only for Cust b)
> Tried vlookup but stops first time it hits the p/n. I think it would be
> SUMPRODUCT or SUMIF but I'm not sure how that would be written. Thanks
>
> Monthly sheet layout
> Cust P/N Qty
> a 1-1 4
> a 1-2 3
> b 1-1 8
> b 1-2 7
>
> New Sheet Layout (Customers are also grouped)
> Cust P/N Jan Qty Feb Qty
> a 1-1
> a 1-2
> b 1-1
> b 1-2

Thanks for the help I'll try it out today.


"Max" wrote:

> One play to try ..
>
> Assume the monthly sheets
> are named as: Jan, Feb, etc
> with tables in cols A to C, data from row2 down, viz:
>
> > Cust P/N Qty
> > a 1-1 4
> > a 1-2 3
> > b 1-1 8
> > b 1-2 7
>
> In your new sheet layout below,
> let's revise the headers for cols C, D (in C1, D1, ... across)
> from: "Jan Qty", "Feb Qty", etc
> to just: Jan, Feb, etc (consistent with the actual sheetnames)
>
> (Above will simplify it for us to use INDIRECT to read the col headers)
>
> > Cust P/N Jan Qty Feb Qty
> > a 1-1
> > a 1-2
> > b 1-1
> > b 1-2
>
> Put in C2, and array-enter (press CTRL+SHIFT+ENTER):
>
> =INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A
> 2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0))
>
> Copy C2 across and fill down to populate the table
>
> Adapt the ranges: A2:A100, C2:C100, etc to suit
>
> And perhaps better with an error trap to return blanks: "" instead of errors
> for a much cleaner looking output, we could put instead in C2, and
> array-enter:
>
> =IF(ISERROR(MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"
> &C$1&"'!B2:B100"),0)),"",INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$
> B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0)))
>
> Then just copy C2 across and fill down as before to populate the table
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steve"
> <[email protected]@discussions.microsoft.com>
> wrote in message news:[email protected]...
> > I'm not too sure if this would be a formula or VB thing. I am making a
> > Yearly sales report/forecast. What I need to do is look up each part
> number
> > and pull the qty from each but only for specific customer (EX: Qty for p/n
> > 1-1 but only for Cust b)
> > Tried vlookup but stops first time it hits the p/n. I think it would be
> > SUMPRODUCT or SUMIF but I'm not sure how that would be written. Thanks
> >
> > Monthly sheet layout
> > Cust P/N Qty
> > a 1-1 4
> > a 1-2 3
> > b 1-1 8
> > b 1-2 7
> >
> > New Sheet Layout (Customers are also grouped)
> > Cust P/N Jan Qty Feb Qty
> > a 1-1
> > a 1-2
> > b 1-1
> > b 1-2
>
>
>

You're welcome
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steve"
<[email protected]@discussions.microsoft.com>
wrote in message news:[email protected]...
> Thanks for the help I'll try it out today.

I'm not too sure if this would be a formula or VB thing. I am making a
Yearly sales report/forecast. What I need to do is look up each part number
and pull the qty from each but only for specific customer (EX: Qty for p/n
1-1 but only for Cust b)
Tried vlookup but stops first time it hits the p/n. I think it would be
SUMPRODUCT or SUMIF but I'm not sure how that would be written. Thanks

Monthly sheet layout
Cust P/N Qty
a 1-1 4
a 1-2 3
b 1-1 8
b 1-2 7

New Sheet Layout (Customers are also grouped)
Cust P/N Jan Qty Feb Qty
a 1-1
a 1-2
b 1-1
b 1-2

One play to try ..

Assume the monthly sheets
are named as: Jan, Feb, etc
with tables in cols A to C, data from row2 down, viz:

> Cust P/N Qty
> a 1-1 4
> a 1-2 3
> b 1-1 8
> b 1-2 7

In your new sheet layout below,
let's revise the headers for cols C, D (in C1, D1, ... across)
from: "Jan Qty", "Feb Qty", etc
to just: Jan, Feb, etc (consistent with the actual sheetnames)

(Above will simplify it for us to use INDIRECT to read the col headers)

> Cust P/N Jan Qty Feb Qty
> a 1-1
> a 1-2
> b 1-1
> b 1-2

Put in C2, and array-enter (press CTRL+SHIFT+ENTER):

=INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A
2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0))

Copy C2 across and fill down to populate the table

Adapt the ranges: A2:A100, C2:C100, etc to suit

And perhaps better with an error trap to return blanks: "" instead of errors
for a much cleaner looking output, we could put instead in C2, and
array-enter:

=IF(ISERROR(MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"
&C$1&"'!B2:B100"),0)),"",INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$
B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0)))

Then just copy C2 across and fill down as before to populate the table
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steve"
<[email protected]@discussions.microsoft.com>
wrote in message news:[email protected]...
> I'm not too sure if this would be a formula or VB thing. I am making a
> Yearly sales report/forecast. What I need to do is look up each part
number
> and pull the qty from each but only for specific customer (EX: Qty for p/n
> 1-1 but only for Cust b)
> Tried vlookup but stops first time it hits the p/n. I think it would be
> SUMPRODUCT or SUMIF but I'm not sure how that would be written. Thanks
>
> Monthly sheet layout
> Cust P/N Qty
> a 1-1 4
> a 1-2 3
> b 1-1 8
> b 1-2 7
>
> New Sheet Layout (Customers are also grouped)
> Cust P/N Jan Qty Feb Qty
> a 1-1
> a 1-2
> b 1-1
> b 1-2

Thanks for the help I'll try it out today.


"Max" wrote:

> One play to try ..
>
> Assume the monthly sheets
> are named as: Jan, Feb, etc
> with tables in cols A to C, data from row2 down, viz:
>
> > Cust P/N Qty
> > a 1-1 4
> > a 1-2 3
> > b 1-1 8
> > b 1-2 7
>
> In your new sheet layout below,
> let's revise the headers for cols C, D (in C1, D1, ... across)
> from: "Jan Qty", "Feb Qty", etc
> to just: Jan, Feb, etc (consistent with the actual sheetnames)
>
> (Above will simplify it for us to use INDIRECT to read the col headers)
>
> > Cust P/N Jan Qty Feb Qty
> > a 1-1
> > a 1-2
> > b 1-1
> > b 1-2
>
> Put in C2, and array-enter (press CTRL+SHIFT+ENTER):
>
> =INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A
> 2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0))
>
> Copy C2 across and fill down to populate the table
>
> Adapt the ranges: A2:A100, C2:C100, etc to suit
>
> And perhaps better with an error trap to return blanks: "" instead of errors
> for a much cleaner looking output, we could put instead in C2, and
> array-enter:
>
> =IF(ISERROR(MATCH($A2&"_"&$B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"
> &C$1&"'!B2:B100"),0)),"",INDEX(INDIRECT("'"&C$1&"'!C2:C100"),MATCH($A2&"_"&$
> B2,INDIRECT("'"&C$1&"'!A2:A100")&"_"&INDIRECT("'"&C$1&"'!B2:B100"),0)))
>
> Then just copy C2 across and fill down as before to populate the table
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Steve"
> <[email protected]@discussions.microsoft.com>
> wrote in message news:[email protected]...
> > I'm not too sure if this would be a formula or VB thing. I am making a
> > Yearly sales report/forecast. What I need to do is look up each part
> number
> > and pull the qty from each but only for specific customer (EX: Qty for p/n
> > 1-1 but only for Cust b)
> > Tried vlookup but stops first time it hits the p/n. I think it would be
> > SUMPRODUCT or SUMIF but I'm not sure how that would be written. Thanks
> >
> > Monthly sheet layout
> > Cust P/N Qty
> > a 1-1 4
> > a 1-2 3
> > b 1-1 8
> > b 1-2 7
> >
> > New Sheet Layout (Customers are also grouped)
> > Cust P/N Jan Qty Feb Qty
> > a 1-1
> > a 1-2
> > b 1-1
> > b 1-2
>
>
>

You're welcome
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Steve"
<[email protected]@discussions.microsoft.com>
wrote in message news:[email protected]...
> Thanks for the help I'll try it out today.