VBA in Excel filtering a sheet based on another sheets values
Posted on 2013-05-15
I have three sheets in my workbook:
The ProductList sheet only contains one column and 3 - 11 rows typically, depending on what the process is waiting to consume from the Output sheet. (it gets turned into a flat file and processed downstream, which again we have no control over) If we send it all 4,000 products - all of them will be processed which will get me fired.
The values sheet is populated with an array of information on products from an existing macro that cannot be edited since it is from our vendor. So in this sheet it has product name, price, product ID, description, category, etc.
The Output sheet has to retrieve data from the Values sheet and do substitutions, math, etc. on them based on the product which all works fine. BUT we do not want all the products listed in the output, just what we want to process at the time. We need the price, description, product ID, etc. but the product name can only match those in the ProductList sheet.
We list 3 things in the ProductList sheet:
We need to limit the Output sheet to only contain those three types of items. In the Values sheet there could be 45 different bearings, 20 mufflers, 9 belts, 50 tires, and 10 motors.
Each of those items having all the attributes mentioned before: price, description, product ID, category, etc. (All data looks identical in terms of datatype and all have the values filled in for price, description, product ID, category)
How can we build into a macro to only pull from the Values sheet what exists in the ProductList sheet (which will then be pushed to the Output sheet after some processing)?
This is an automated process whereby the user enters the category of the items he/she wants processed in the ProductList sheet then clicks a button attached to a macro that kicks off different processing.
Any help would be greatly appreciated.
Thanks in advance,