Hello all,
I'm a longtime user of this forum, but this is the first thing specific enough that I needed to ask your guys' help.
I have an inventory report I've created that is referencing different weekly worksheets within the excel workbook, each labeled 20, 21, 23 etc. I've created a way to not use macros that has allowed me to do everything I need, except for this one part:
Here is the way my table is setup: I'm having it list from highest excess inv. value to lowest.
Week: [23]
G H I J
5 Manager | Product ID | Product Name | Excess Inventory Value
6 Joe Blow 11111 Name1 $230,000
7 Jane Smith 11142 Different Name $190,000
8 Jenny Cox 32544 Other Name $170,000
Manager Formula for G6: =VLOOKUP(H6,INDIRECT($G$2&"!"&"b:v"),21,0)
Product ID Formula for H6: =INDEX(INDIRECT($G$2&"!"&"$B$2:$B$10000"),MATCH(1,INDEX((INDIRECT($G$2&"!"&"$U$2:$U$10000")=LARGE(INDIRECT($G$2&"!"&"$U$2:$U$10000"),ROWS(H$5:H5)))*(COUNTIF(H$5:H5,INDIRECT($G$2&"!"&"$B$2:$B$10000"))=0),),0))
Product Name Formula for I6: =VLOOKUP(H6,INDIRECT($G$2&"!"&"b:d"),3,0)
Excess Value Formula for J6 =VLOOKUP(H6,INDIRECT($G$2&"!"&"b:u"),20,0)
All of the VLOOKUP formulas are easy, but what I need help with is the Product ID Formula (everything else flows from there). I want it to only list the Product IDs that are for a specific Manager. Right now everything is working perfectly, except I cannot figure out a way to get it to only list for 1 manager, instead of just listing all of them.
On the data worksheets, Column B is Product IDs, Column U is the Excess Inventory Level, and Column V is the Manager Name.
Thank you in advance for any help! I'm going nuts.
I'm a longtime user of this forum, but this is the first thing specific enough that I needed to ask your guys' help.
I have an inventory report I've created that is referencing different weekly worksheets within the excel workbook, each labeled 20, 21, 23 etc. I've created a way to not use macros that has allowed me to do everything I need, except for this one part:
Here is the way my table is setup: I'm having it list from highest excess inv. value to lowest.
Week: [23]
G H I J
5 Manager | Product ID | Product Name | Excess Inventory Value
6 Joe Blow 11111 Name1 $230,000
7 Jane Smith 11142 Different Name $190,000
8 Jenny Cox 32544 Other Name $170,000
Manager Formula for G6: =VLOOKUP(H6,INDIRECT($G$2&"!"&"b:v"),21,0)
Product ID Formula for H6: =INDEX(INDIRECT($G$2&"!"&"$B$2:$B$10000"),MATCH(1,INDEX((INDIRECT($G$2&"!"&"$U$2:$U$10000")=LARGE(INDIRECT($G$2&"!"&"$U$2:$U$10000"),ROWS(H$5:H5)))*(COUNTIF(H$5:H5,INDIRECT($G$2&"!"&"$B$2:$B$10000"))=0),),0))
Product Name Formula for I6: =VLOOKUP(H6,INDIRECT($G$2&"!"&"b:d"),3,0)
Excess Value Formula for J6 =VLOOKUP(H6,INDIRECT($G$2&"!"&"b:u"),20,0)
All of the VLOOKUP formulas are easy, but what I need help with is the Product ID Formula (everything else flows from there). I want it to only list the Product IDs that are for a specific Manager. Right now everything is working perfectly, except I cannot figure out a way to get it to only list for 1 manager, instead of just listing all of them.
On the data worksheets, Column B is Product IDs, Column U is the Excess Inventory Level, and Column V is the Manager Name.
Thank you in advance for any help! I'm going nuts.
