Create sequential IDs based on columns responses

Trying to create automatic sequential ID numbers based on the date to ship and where the sample goes.

Have a general auto numbering Row ID, have (thanks to reading other questions and responses) figured out how to designate a year. Now I would like to number the Internal (INT) shipments separately from the external Shipments. I just can't make the leap to this from the discussions I'm seeing but suspect I need some IF statements.

Here's my formula right now that works to give me a running total of shipments: —see clip below. it is assigning the last 4 digits independently of if EXT or INT. Any help appreciated!

=IF([To whom will the sample go?]@row = "Internal", "INT", "EXT") + "-" + RIGHT(YEAR([SHIP DATE]@row ), 2) + "-" + RIGHT("000" + COUNTIFS([SHIP DATE]:[SHIP DATE], IFERROR(YEAR(@cell ), 0) = YEAR([SHIP DATE]@row ), [Row ID]:[Row ID], @cell <= [Row ID]@row ), 4)

Find more posts tagged with

Formulas

Automations

Accepted answers

Paul Newcome

Inside of the COUNTIFS, you would add a range of [To whom will the sample go?]:[To whom will the sample go?], and the criteria for that range would be [To whom will the sample go?]@row.

=IF([To whom will the sample go?]@row = "Internal", "INT", "EXT") + "-" + RIGHT(YEAR([SHIP DATE]@row), 2) + "-" + RIGHT("000" + COUNTIFS([SHIP DATE]:[SHIP DATE], IFERROR(YEAR(@cell), 0) = YEAR([SHIP DATE]@row), [Row ID]:[Row ID], @cell <= [Row ID]@row, [To whom will the sample go?]:[To whom will the sample go?], @cell = [To whom will the sample go?]@row), 4)

All comments

Paul Newcome

Try including a range/criteria set within your COUNTIFS to also evaluate the [To whom will the sample go?]:[To whom will the sample go?] column for [To whom will the sample go?]@row.

Tammy D

Thanks Paul. This is where the wheels come off for me! I'm not sure how to do thatin the current formula:

Paul Newcome

Inside of the COUNTIFS, you would add a range of [To whom will the sample go?]:[To whom will the sample go?], and the criteria for that range would be [To whom will the sample go?]@row.

=IF([To whom will the sample go?]@row = "Internal", "INT", "EXT") + "-" + RIGHT(YEAR([SHIP DATE]@row), 2) + "-" + RIGHT("000" + COUNTIFS([SHIP DATE]:[SHIP DATE], IFERROR(YEAR(@cell), 0) = YEAR([SHIP DATE]@row), [Row ID]:[Row ID], @cell <= [Row ID]@row, [To whom will the sample go?]:[To whom will the sample go?], @cell = [To whom will the sample go?]@row), 4)

Tammy D

Thanks! It works

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.