Formula to create a number assignment catalog

Aaron Olsen

Hello,

I am trying to create a number assignment catalog that is automated to speak to a smartsheet that has my full collection of products. The goal is when creating a new product anyone could fill out a form select the category numbers that will define the department and then select a a number that will identify the specific product. A lot of numbers are already in use so the formula needs to reference a sheet that has existing numbers in use and only allow available numbers to be selected. I have attached a screenshot of what the number assignment could look like but am lost as to how to write the formula that will communicate to the broader sheet. The first 4 digits are the category assignments and the last four are the numbers in question. In this instance there is a letter that will be common in each selection. Any options would be super helpful. Thanks, Aaron

Annotation 2020-10-21 143206.jpg

Mary_A

You can do this by creating a primary column which combines values from two other columns. I would likely create aProduct Listsheet that automatically assigns the proper number to a new product. TheProduct Listwould have aCreate New Productform which asks for the category.

In the Product List sheet, create anAUTOIDcolumn with this structure.

Create a column of typeDropdown (Single Select)with each potential category. In this caseXX01,XX02, and so forth.

For the primary column, I'd rename it toProduct IDand put a column formula in it:

=Category@row + "-" + AUTOID@row

Populate theProduct Listsheet with all the existing products.

Create a form for users to add a new product.

Because you populated the product list already, adding a new product with the form will automatically assign a unique and new product number

Mary_A

You can do this by creating a primary column which combines values from two other columns. I would likely create aProduct Listsheet that automatically assigns the proper number to a new product. TheProduct Listwould have aCreate New Productform which asks for the category.

In the Product List sheet, create anAUTOIDcolumn with this structure.

Create a column of typeDropdown (Single Select)with each potential category. In this caseXX01,XX02, and so forth.

For the primary column, I'd rename it toProduct IDand put a column formula in it:

=Category@row + "-" + AUTOID@row

Populate theProduct Listsheet with all the existing products.

Create a form for users to add a new product.

Because you populated the product list already, adding a new product with the form will automatically assign a unique and new product number

Aaron Olsen

Thank you! I started on this track but couldn't figure it out. This is very helpful I appreciate your level of detail.