Excel ‘07
SCENARIO A:
I have 4 worksheets with the following columns in each (all are text):
A: Comp
B: CompDesc
C: Material
D: MatDesc
The Worksheets are named 01, 02, 03, 04.
I have named ranges that cover each column of data – _01Comp, _01CompDesc, _01Material, _01MatDesc – with the number changing depending on the worksheet (_02Comp, _03Comp, etc). The named ranges do not include the column heading (ex. _04Comp = $A$2:$A$111).
Sheet 01 is an export of data that shows what assembly each component is used in (one component can go into mult assemblies and each assembly can have more than one of the components in it). Sheet 02 takes the list of assemblies from column C in sheet 01, filtered down to a unique list, and pulls a new listing of where used. Sheet 03 does the same from the sheet 02 data and sheet 04 does the same with the 03.
Not every component goes all the way to the 4th set so the ‘end’ may not only be in the 4th tab.
I ultimately need to identify all the final assemblies that each component goes into.
I’m not sure if the best way to handle this is with a formula (or a series or them) or with VBA. I am fairly comfortable working with code that has already been written but have never written any myself.
Any help, assistance, advice, direction would be hugely appreciated.
Thank you,
Tanya
SCENARIO A:
I have 4 worksheets with the following columns in each (all are text):
A: Comp
B: CompDesc
C: Material
D: MatDesc
The Worksheets are named 01, 02, 03, 04.
I have named ranges that cover each column of data – _01Comp, _01CompDesc, _01Material, _01MatDesc – with the number changing depending on the worksheet (_02Comp, _03Comp, etc). The named ranges do not include the column heading (ex. _04Comp = $A$2:$A$111).
Sheet 01 is an export of data that shows what assembly each component is used in (one component can go into mult assemblies and each assembly can have more than one of the components in it). Sheet 02 takes the list of assemblies from column C in sheet 01, filtered down to a unique list, and pulls a new listing of where used. Sheet 03 does the same from the sheet 02 data and sheet 04 does the same with the 03.
Not every component goes all the way to the 4th set so the ‘end’ may not only be in the 4th tab.
I ultimately need to identify all the final assemblies that each component goes into.
I’m not sure if the best way to handle this is with a formula (or a series or them) or with VBA. I am fairly comfortable working with code that has already been written but have never written any myself.
Any help, assistance, advice, direction would be hugely appreciated.
Thank you,
Tanya
