Hi! I'm struggling to think of a way to properly formulate a title for this, so if you have any recommendations let me know. Let me explain the issue I'm having!
I have a list including all parts for the project structured in a specific way, where parts numbers often show up multiple times, and are nested below other assemblies. There are a lot more columns and items than I've shown here, but the same solution would work for both.
On a second sheet, I need to include a list of all unique part numbers without any duplicates, which I achieve using a UNIQUE function. In an adjacent column I need to be able to input the status of each part manually.
On the first sheet, each part number needs to display the status pulled from the second sheet.
The intended workflow is for one group of people to add new parts to the first sheet without ever needing to modify the second sheet, and for a different group of people to modify the status of each part in the second sheet without ever needed to look at the first sheet.
This workbook must be able to handle the addition of new part numbers to the first sheet, inserted at any row. This is where the problem arises.
When inserting a new part number somewhere in the middle of the first sheet, the unique array on the second sheet will update to include the new part number, but the status input values do not shift to stay with their part numbers. This means that now the manually input status values are incorrect. I've taken some screenshots to hopefully show what I am referring to:
Before new part number is added to first sheet:
After new part number ("NEW PART TEST") is added to first sheet:
I realize that I could change it so that the status is input manually into the first sheet and then pulled from there into the second sheet, however that is not the desired workflow and would mean we would have to change the status on all instances of that part number instead of just one.
I've also included mini-sheets below. I couldn't find a way to include multiple sheets on one mini-sheet, so included them separately. If you need anything else from me please let me know.
Thank you for your time and any help you can offer!
I have a list including all parts for the project structured in a specific way, where parts numbers often show up multiple times, and are nested below other assemblies. There are a lot more columns and items than I've shown here, but the same solution would work for both.
On a second sheet, I need to include a list of all unique part numbers without any duplicates, which I achieve using a UNIQUE function. In an adjacent column I need to be able to input the status of each part manually.
On the first sheet, each part number needs to display the status pulled from the second sheet.
The intended workflow is for one group of people to add new parts to the first sheet without ever needing to modify the second sheet, and for a different group of people to modify the status of each part in the second sheet without ever needed to look at the first sheet.
This workbook must be able to handle the addition of new part numbers to the first sheet, inserted at any row. This is where the problem arises.
When inserting a new part number somewhere in the middle of the first sheet, the unique array on the second sheet will update to include the new part number, but the status input values do not shift to stay with their part numbers. This means that now the manually input status values are incorrect. I've taken some screenshots to hopefully show what I am referring to:
Before new part number is added to first sheet:
After new part number ("NEW PART TEST") is added to first sheet:
I realize that I could change it so that the status is input manually into the first sheet and then pulled from there into the second sheet, however that is not the desired workflow and would mean we would have to change the status on all instances of that part number instead of just one.
I've also included mini-sheets below. I couldn't find a way to include multiple sheets on one mini-sheet, so included them separately. If you need anything else from me please let me know.
| Book1 | ||||
|---|---|---|---|---|
| B | C | |||
| 2 | PART NUMBER | STATUS | ||
| 3 | 7040AA | In-progress | ||
| 4 | 7041AA | In-progress | ||
| 5 | 5624AA | Complete | ||
| 6 | ||||
| 7 | 7041AA | In-progress | ||
| 8 | 5624AA | Complete | ||
| 9 | 5629 | In-progress | ||
| 10 | 5628 | In-progress | ||
| 11 | 5750 | In-progress | ||
| 12 | 7405 | In-progress | ||
| 13 | ||||
| 14 | 5624AA | Complete | ||
| 15 | 5623 | Complete | ||
| 16 | 5622 | Complete | ||
| 17 | ||||
| 18 | 5624AA | Complete | ||
| 19 | 5623 | Complete | ||
| 20 | 5622 | Complete | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:C20 | C3 | =IFERROR(INDEX(Sheet3!$C$3:$C$30,MATCH(B3,Sheet3!$B$3:$B$30,0)),"") |
| Book1 | ||||
|---|---|---|---|---|
| B | C | |||
| 2 | PART NUMBER | STATUS | ||
| 3 | 7040AA | In-progress | ||
| 4 | 7041AA | In-progress | ||
| 5 | 5624AA | Complete | ||
| 6 | 5629 | In-progress | ||
| 7 | 5628 | In-progress | ||
| 8 | 5750 | In-progress | ||
| 9 | 7405 | In-progress | ||
| 10 | 5623 | Complete | ||
| 11 | 5622 | Complete | ||
| 12 | ||||
| 13 | ||||
| 14 | ||||
| 15 | ||||
| 16 | ||||
| 17 | ||||
| 18 | ||||
| 19 | ||||
| 20 | ||||
Sheet3 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B3:B11 | B3 | =UNIQUE(FILTER(Sheet1!$B$3:$B$30,(Sheet1!$B$3:$B$30<>"")),FALSE,FALSE) |
| Dynamic array formulas. | ||
Thank you for your time and any help you can offer!
