Hi All,
I'm an experienced Excel user but I'm trying to fill in some knowledge gaps and use a current problem to do that. Apologies for the length. It's complicated and I'm stumped. I would really appreciate any help!
Background:
I'm updating a workbook model I inherited. I've created an Instructions sheet.
There is currently an INDEX sheet that automatically lists all worksheet names in a list in column A. There is no column heading in A.
There are bill-of-material sheets for each finished goods item and the sheet name is the FG part number. So, the INDEX list has all the FG part numbers in the list in column A. All BOM sheets are listed at the far right of the workbook in FG part number alphabetic order. So, all FG part numbers will start at some row in column A.
Since new sheets may be added, the row the FG part numbers begin may change on the INDEX sheet.
A sheet called "Normalized Data BOM" lists the FG part number, description then lists the material components from each individual BOM sheets. So, the FG number will appear on 14 rows, then the next FG number will show for 14 rows, etc.
Problem:
The previous model builder went to the INDEX sheet, found the FG numbers wherever they were at the time, copied and pasted them. Then, in the "Normalized Data BOM" sheet linked the first FG number to the specific cell of the pasted INDEX sheet. Now there are new BOM's causing a new pasted list to be out of order and longer. This means I'll have to add new rows on the Normalized Data BOM sheet and that's ok. I could again copy the FG numbers from the INDEX sheet and paste over the old pasted list, then add rows in the Normalized Data BOM sheet.
Requested Solution:
1) Ideally I would like to enter the first FG number on the Normalized Data BOM sheet and have all other FG numbers update down the rows in the Normalized Data BOM sheet. I would still need to add rows on the Data BOM sheet but that's ok. I thought this might be done by using a named range on the INDEX sheet to include all FG part numbers. Then, use something like INDEX, MATCH, OFFSET, etc. to have the FG numbers auto-populate on the Normalized Data BOM sheet. Is there a way to do this?
2) If used the formulas I mentioned, how could I quickly enter the formulas for the 600+ FG part numbers in the Normalized Data BOM sheet?
Below are screenshots of the INDEX and Normalized Data BOM sheets.
On the INDEX sheet, the first FG appears on row 31 this time.
Excel 2010
<tbody>
</tbody>
On the Normlized data BOM sheet the first finished good number needs to be in A10 and the next FG number in A24, etc.:
Excel 2010
<tbody>
</tbody>
I cannot think of any unique identifiers for FG part numbers. For example they do not all have dashes "-", they do not all have uppercase, they do not all have the same number of characters. I could manually use column B on the INDEX sheet to enter something like "FG".
It might just be quicker and easier to use the copy-paste to a new list method. I'd just like to use this as a thought experiment to see if it can be done. And, I dislike having duplicate data lists and linking to a secondary list versus the main source.
I would very much appreciate any help you can provide. Thank you!
I'm an experienced Excel user but I'm trying to fill in some knowledge gaps and use a current problem to do that. Apologies for the length. It's complicated and I'm stumped. I would really appreciate any help!
Background:
I'm updating a workbook model I inherited. I've created an Instructions sheet.
There is currently an INDEX sheet that automatically lists all worksheet names in a list in column A. There is no column heading in A.
There are bill-of-material sheets for each finished goods item and the sheet name is the FG part number. So, the INDEX list has all the FG part numbers in the list in column A. All BOM sheets are listed at the far right of the workbook in FG part number alphabetic order. So, all FG part numbers will start at some row in column A.
Since new sheets may be added, the row the FG part numbers begin may change on the INDEX sheet.
A sheet called "Normalized Data BOM" lists the FG part number, description then lists the material components from each individual BOM sheets. So, the FG number will appear on 14 rows, then the next FG number will show for 14 rows, etc.
Problem:
The previous model builder went to the INDEX sheet, found the FG numbers wherever they were at the time, copied and pasted them. Then, in the "Normalized Data BOM" sheet linked the first FG number to the specific cell of the pasted INDEX sheet. Now there are new BOM's causing a new pasted list to be out of order and longer. This means I'll have to add new rows on the Normalized Data BOM sheet and that's ok. I could again copy the FG numbers from the INDEX sheet and paste over the old pasted list, then add rows in the Normalized Data BOM sheet.
Requested Solution:
1) Ideally I would like to enter the first FG number on the Normalized Data BOM sheet and have all other FG numbers update down the rows in the Normalized Data BOM sheet. I would still need to add rows on the Data BOM sheet but that's ok. I thought this might be done by using a named range on the INDEX sheet to include all FG part numbers. Then, use something like INDEX, MATCH, OFFSET, etc. to have the FG numbers auto-populate on the Normalized Data BOM sheet. Is there a way to do this?
2) If used the formulas I mentioned, how could I quickly enter the formulas for the 600+ FG part numbers in the Normalized Data BOM sheet?
Below are screenshots of the INDEX and Normalized Data BOM sheets.
On the INDEX sheet, the first FG appears on row 31 this time.
Excel 2010
| A | |
|---|---|
| 31 | A0280-SP |
| 32 | A2013-SP |
| 33 | ABP |
| 34 | Akoya |
| 35 | B0206-BO |
| 36 | B0206-BW |
| 37 | B1000 |
| 38 | B-1002-SP |
| 39 | B-1038 |
| 40 | B1222-SPO |
| 41 | B1222-SPW |
| 42 | B1304-SPO |
<tbody>
</tbody>
INDEX
On the Normlized data BOM sheet the first finished good number needs to be in A10 and the next FG number in A24, etc.:
Excel 2010
| A | |
|---|---|
| 8 | |
| 9 | LookupKey |
| 10 | A0280-SP |
| 11 | A0280-SP |
| 12 | A0280-SP |
| 13 | A0280-SP |
| 14 | A0280-SP |
| 15 | A0280-SP |
| 16 | A0280-SP |
| 17 | A0280-SP |
| 18 | A0280-SP |
| 19 | A0280-SP |
| 20 | A0280-SP |
| 21 | A0280-SP |
| 22 | A0280-SP |
| 23 | A0280-SP |
| 24 | A2013-SP |
| 25 | A2013-SP |
| 26 | A2013-SP |
| 27 | A2013-SP |
| 28 | A2013-SP |
| 29 | A2013-SP |
| 30 | A2013-SP |
| 31 | A2013-SP |
| 32 | A2013-SP |
| 33 | A2013-SP |
| 34 | A2013-SP |
| 35 | A2013-SP |
| 36 | A2013-SP |
| 37 | A2013-SP |
| 38 | ABP |
| 39 | ABP |
| 40 | ABP |
<tbody>
</tbody>
Normalized Data BOM
I cannot think of any unique identifiers for FG part numbers. For example they do not all have dashes "-", they do not all have uppercase, they do not all have the same number of characters. I could manually use column B on the INDEX sheet to enter something like "FG".
It might just be quicker and easier to use the copy-paste to a new list method. I'd just like to use this as a thought experiment to see if it can be done. And, I dislike having duplicate data lists and linking to a secondary list versus the main source.
I would very much appreciate any help you can provide. Thank you!
