How to use predecessors to auto update Part Numbers?
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?
Best Answer

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

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]: AutoNumber 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.

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?

Try indenting the rows containing the parts as child rows underneath of the version row.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!