I am envisioning a way that will allow me to choose an input option from a drop down box (preferred) or a list.
Once clicked, it will run a macro in the background and will go find every component that goes into the selected input option. Then it will go to a different tab and copy and paste selected cells that contain the details for each of those components.
Tab 1: "Dropbox"
Tab 2: "BOM with Take Rate"
Tab 3: "Component Cost Breakdown"
Example
On tab 1 I selected Program A. Within Program A, there are Components 1-5 and 8-10 which is found on Tab 2. On Tab 2 the programs are in cells BN7:CQ7. The macro should look up the option selected in this range on tab 2 and then look at the quantities in rows 7:206 in the selected program's column. Also on Tab 2 can be found the part number for each component in column C. The macro should see the option selected on Tab 1 go to tab 2 in range BN7:CQ7 and find the option selected. It should then look down at each component (rows 7:206) within the selected program to see if there is a quantity greater than 0. If the component quantity is greater than 0 the macro should then look to that row in column C to get the component part number.
From here the macro should have a list of all the component part numbers located within the selected program. The macro should then look at Tab 3 in column C. The macro should grab all the cells between columns E and V as well as column AP wherever that part number is found in column C. The macro should then place that collected input back on tab 1 starting in cell A10
Please let me know if further explanation is needed!
Once clicked, it will run a macro in the background and will go find every component that goes into the selected input option. Then it will go to a different tab and copy and paste selected cells that contain the details for each of those components.
Tab 1: "Dropbox"
Tab 2: "BOM with Take Rate"
Tab 3: "Component Cost Breakdown"
Example
On tab 1 I selected Program A. Within Program A, there are Components 1-5 and 8-10 which is found on Tab 2. On Tab 2 the programs are in cells BN7:CQ7. The macro should look up the option selected in this range on tab 2 and then look at the quantities in rows 7:206 in the selected program's column. Also on Tab 2 can be found the part number for each component in column C. The macro should see the option selected on Tab 1 go to tab 2 in range BN7:CQ7 and find the option selected. It should then look down at each component (rows 7:206) within the selected program to see if there is a quantity greater than 0. If the component quantity is greater than 0 the macro should then look to that row in column C to get the component part number.
From here the macro should have a list of all the component part numbers located within the selected program. The macro should then look at Tab 3 in column C. The macro should grab all the cells between columns E and V as well as column AP wherever that part number is found in column C. The macro should then place that collected input back on tab 1 starting in cell A10
Please let me know if further explanation is needed!
