AutoNumber Column Based on Another Column

RiseUpPNW

I have tried to follow other threads asking similar questions, but I'll admit, I'm lost and any attempt at following those formulas has not resulted in what I need!

We currently have a request form and have each submission set to assign an auto number to the request. Which is ok, but we really don't want them to be numbered in order of when they were received as much as in order of TYPE and then when it was received.

For example, a check request would be number CH - 1, the next Check request will be Ch - 2 and so forth. However, if it is an online request, we'd like those to be numbered ON - 1, ON - 2 and so forth.

Please help, and I'm very new to this so please keep that in mind! :) I have an example, showing what we currently have vs what we WANT. Thank you.

TEST.png

Kelly Moore

Hey @RiseUpPNW

I'm happy to help. Thanks so much for the screenshot - it is ALWAYS so helpful when added to any post.

Try this

=UPPER(LEFT([Type Request]@row, 2)) + "-" + COUNTIFS([Type Request]:[Type Request], [Type Request]@row, [Auto #]:[Auto #], @cell <= [Auto #]@row)

The UPPER function forces the text to upper case. The Left is stripping the first two characters out of the [Type Request] column. The COUNTIFS is counting the Types down the sheet based on your Auto number field

Does this work for you?
Kelly

RiseUpPNW

Oh my goodness, that's amazing. I particularly LOVE and thank you so much that you took the time to explain what each function was doing! I've been learning as I go so this is extremely helpful! It worked perfectly and I learned a few functions I didn't even know were possibilities. Thank you!

One more question if you don't mind, what is the point of referencing the Auto column in this scenario. Since it's using it's own auto numbering system to number based on the Type Request, is the Auto numbering column even needed? I tried taking it out, and used JUST this: =UPPER(LEFT([Type Request]@row, 2)) + "-" + COUNTIFS([Type Request]:[Type Request], [Type Request]@row) and it seems to work the same?

Kelly Moore

@RiseUpPNW

The reference to the rowid is to find the countifs at that position on the sheet. Without a marker that says count up to this point, all of your countifs, for that Type, would have the same number. And that number would dynamically change as more of the same types were added. Perhaps your other column was already doing that and I didn’t really notice

Kelly