Auto Add a Number After Auto Numbering

I have a master sheet which captures data and assigns an auto-number (Incident Number). When a row falls into a certain category (Warranty), it gets copied to a new sheet.

I want to be able to take that unique Incident Number and add to it. This is what I have so far:

It puts a prefix onto the Incident Number, but I also want it to add a letter or number at the end. So it would read W-CIR-00001-A.

Any ideas would be much appreciated.

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

What would be the logic? Would it always be "A", or would you need different letters based on certain variables?

killis_

It would need to be a consecutive letter (or number). It just needs to be something that can uniquely identify the row.

What happens is we get a warranty claim, but there could be multiple items on it. We need to break that claim up per item, i.e. create a row for each item within the claim so that we can identify them individually.

Paul Newcome

A number would definitely be much easier. Would you need it prefilled with leading zeros such as 001, 002 or just 1, 2 is fine?

killis_

Preferably with leading zeros, this will give us a bit of scope if there are lots of items.

Paul Newcome

Ok. Add this to the end of your existing formula:

+ IF(COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)< 10, "00", IF(COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)< 100, "0", "")) + COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)

This will prefill with up to 2 zeros so that you have capacity for up to 999 entries per incident number.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.