Formula to create a number assignment catalog part 2

Aaron Olsen

Hello,

I have created a number assignment catalog but am hoping there is a formula that can adjust how these numbers are assigned. What I am trying to accomplish is: When the form is filled out for a new product with the Department Name and Category name. that the fixture number will auto fill with a numeric value XXXX the exception being when more than one Department Name is selected that the Fixture Number will auto fill with a numeric value beginning with TXXX. I have attached some images to show my layout and form. Any information you can provide is much appreciated.

Thanks!

complete fixture #.jpg

Form .jpg

Ramzi K

@Aaron Olsen

Are you asking if the form can fill out the values as the user makes entries in it, or if the sheet can autofill after the form is submitted?

Cheers,

Ramzi

Aaron Olsen

@Ramzi K

I am asking if the fixture number can auto assign based on the information that is entered on the form. For example someone selects Department name: 02- At Home, Category- 02 Fixtures Fixture Number auto fill w/ numeric value not being used in four digit format XXXX. Similarly they do the same thing but select two department names the fixture number autofills w/ numeric value no being used but begins with a TXXX. I hope this makes sense. Thanks!

Ramzi K

Yes, you can do this in the sheet after the for is submitted.

You will need a reference sheet with all corresponding values and then use INDEX(MATCH... or VLOOKUP functions to get your values based on the form submission.

I hope that helps.

Ramzi

Aaron Olsen

I am new to VLOOKUP and INDEX/ MATCH and how to write these formulas. I have created a reference sheet of existing fixture #'s. What I am trying to achieve is: have the fixture # auto fill based on information from two forms existing design and new design, Existing design will match the complete fixture # on original sheet and update the fields from the form while maintaining the same complete #, New Design will reference the fixture # reference sheet and assign a unique # (XXXX) based on #'s already in use, The one caveat is if they select more than one department name for the item the number it would still be referenced from the fixture # reference sheet but it should begin with the letter T (TXXX). The fixture # reference sheet will have all the existing fixture #'s being used including all fixtures with a T prior to the number. It will also have fixture names. I have included some screen grabs to help understand what I am trying to accomplish. Thank you!

NEW DESIGN FORM

Design Update form

CURRENT LAYOUT

Genevieve P.

Hi @Aaron Olsen

I noticed that you marked these posts as Not answering your question; were you able to figure out a solution or do you still need help?

Aaron Olsen

@Genevieve P I have spoken with a representative from Smartsheet and they didn't think what I was trying to do is possible currently. If you think you have a solution I am very open to hearing it. Thanks!

Genevieve P.

Hi @Aaron Olsen

Perhaps I'm misunderstanding the set-up, but if you already have a reference sheet that has the Fixture Numbers as well as what Department and Category that's associated with, you can use an INDEX(MATCH as @Ramzi K mentioned above.

In your instance it actually sounds like an INDEX(COLLECT may be better, since you have two criteria (Department and Category).

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

So in your case:

=INDEX(COLLECT({Fixture Number in other sheet}, {Department Column}, [Department Name]@row, {Category Column}, [Category Number]@row), 1)

The only thing is that this won't create new numbers, it will look into your reference sheet and match up the already associated number, whether that has a T or not. Is that what you're looking to do?

Aaron Olsen

I figured out a work around for this. Thanks for all your help!

Andrée Starå

Hi @Aaron Olsen

Excellent!

Feel free to share how you solved it because it might help others in the same situation.

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!