Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Advanced Lookups
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 |
#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 <atyahoo<dotcom ---- "Steve" @d iscussions.microsoft.com wrote in message ... 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 |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- "Steve" @d iscussions.microsoft.com wrote in message ... 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 |
#4
|
|||
|
|||
You're welcome
Thanks for posting back .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve" @d iscussions.microsoft.com wrote in message ... Thanks for the help I'll try it out today. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced filter criteria "begins with" and "does not begin with" | Excel Worksheet Functions | |||
Using advanced filter to search for criteria in a list | Excel Discussion (Misc queries) | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Advanced Find Command?? | Excel Discussion (Misc queries) | |||
Can Excel advanced filter be used in "and NOT" mode | Excel Worksheet Functions |