Good morning all.
I have a challenge for everyone. I need a unique number based on the previous rows unique number that can then be influenced again by another criteria. The "Number" MUST always increase.
The "Identifier" column is the column in question. I have tried multiple was to populate this but I either get a #circular reference or I cannot base it upon the value above and end up with duplicate numbers.
"Collector" column is a simple formula
=IFERROR(INDEX(COLLECT([Next Number]:[Next Number], [Row ID]:[Row ID], [Row ID]:[Row ID] = [Row ID]@row - 1), 1), "")
"Next Number"
=IF(AND([Row ID]@row = 1, [Left/Right]@row = "Right"), 2, IF(AND([Row ID]@row = 1, [Left/Right]@row = "Left"), 1, Next@row ))
and "Next"
This is the one that should allow it to work but returns a circular reference error.
=IFERROR(IF(AND([Left/Right]@row = "Right", ISEVEN(Collector@row )), Collector@row + 2, IF(AND([Left/Right]@row = "Left", ISODD(Collector@row )), Collector@row + 2, Collector@row + 1)), "")
I cannot break this out to another sheet, so no automation to copy row. The automations are not reliable enough and i need a number straight away as this is used for a self service ticket system.
The logic is
If the item is not handed then the next number starting from 1
If the item is Right handed it MUST have the next available EVEN number
If the Item is Left Handed it MUST have the next available ODD number
This does mean 2 odd items in a row would miss out an even number. If you follow the green boxes this is how it "Should" be formed. Iv spent nearly a week on this and have gone formula blind Chat GPT was also no help and the Smartsheet AI isn't powerful enough either. so any help here would be amazing!!!
