How to use predecessors to auto update Part Numbers?

sb_19

For my use case, I have a version of a product that only updates when it changes on the next version.

So for example say I have an assembly that has 3 parts in it. Lets say V1 and V2 of this assembly are identical except 1 item is different so has a different number:

V1:

• 001 [Input]
• 002 [Input]
• 003 [Input]

V2:

• 001 [INDEXED]
• 002 [INDEXED]
• 004 [Input]

V3

• 001 [INDEXED]
• 020 [Input]
• 004 [INDEXED]

I wanted to use predecessors to basically have V1 be inputted text and then have the rest of the numbers just be Indexed from their predecessor and only change if I manually change a number.

So default value for all numbers of parts after V1 is an index pointing to the number of the predecessor unless I manually change it and then it updates all the ones after that.

I got it to work for 1 instance but indexing an index function causes a circular reference. Is there any way around that?

Paul Newcome

Try indenting the rows containing the parts as child rows underneath of the version row.

Paul Newcome

You are going to need some helper columns. I'll lay everything out and then provide a screenshot as well (insert all columns with appropriate headers and formatting before dropping in formulas).

[Manual]: Text / Number column - Manual entry

[Index]: Text / Number column - Formula

=IF(Version@row <> "", IFERROR(INDEX(COLLECT(Manual:Manual, Manual:Manual, @cell <> "", [Part Number]:[Part Number], @cell = [Part Number]@row, Row:Row, @cell <= Row@row), COUNTIFS(Manual:Manual, @cell <> "", [Part Number]:[Part Number], @cell = [Part Number]@row, Row:Row, @cell <= Row@row)), ""))

[Final]: Text / Number column - Formula

=IF(Manual@row <> "", Manual@row, Index@row)

[Version]: Text / Number column - Formula

=IFERROR(PARENT([Primary Column]@row), "")

[Part Number]: Text / Number column - Formula

=IF(Version@row <> "", COUNTIFS(Version:Version, @cell = Version@row, Row:Row, @cell <= Row@row))

[Auto Number]: Auto-Number column - No special formatting

[Row]: Text / Number column - Formula

=MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)

Once set up, you can hide all columns except for Manual and Final.

sb_19

This was super helpful, thank you. I think I almost got it up and running but running into issues because the part numbers are not as simple as the ones I listed as an example. They are actually a random collection of numbers that are assigned to each part (see screenshot)

Notice how the primary column final part numbers for V34 do not match up to V33 even though there is no manual entry?

Paul Newcome

Try indenting the rows containing the parts as child rows underneath of the version row.