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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 08:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com