Possible to automate numbers per row type?

Arianna Sikorski

I am adding a PO system in our smartsheet that holds different types of financial tracking. I want to auto populate new PO numbers, however, only for a row if a different column is marked as "PO Request." Is this possible? Currently the autonumbering feature does all rows.

I've tried the following column formula so that if there are other types, it will show "n/a", then I change the column type to automated numbers and it only counts rows that are blank (which is what I want!). But this only happens if information is already in the sheet. If new submissions are made, then the autonumbering column overrides the formula/function below and every new row after that starts autonumbering. Thoughts? Would there be an automation that could add a number only for specific rows marked as PO? Or a hidden question the submission form that could create numbers when a new submission comes in?

=(IF([Payment Type]@row = "Invoice", "n/a") + IF([Payment Type]@row = "Independent Contractor Work Hours", "n/a") + IF([Payment Type]@row = "Employee Reimbursement", "n/a") + IF([Payment Type]@row = "Constituent Refund", "n/a") + IF([Payment Type]@row = "Employee Credit Card Receipt", "n/a"))

Danielle Arteaga

I think you might be able to leverage an Auto-Number column (which would be hidden) that would work with a formula column to produce your result.

So, for example, you have an Auto-Number column, and it just consecutively numbers every row, regardless of transaction type, using whatever set-up you prefer. But, that column stays hidden. (It's shown in the screen cap below for reference, but it does not need to be visible.)

You create a PO Request column using a checklist column type and insert the following column-level formula:

=IF(OR([Payment Type]@row = "Payment Type A", [Payment Type]@row = "Payment Type B", [Payment Type]@row = "Payment Type C"), 1, 0)

("Payment Type A," "Payment Type B," and "Payment Type C" are just placeholders for the payment types that you DO want to produce a PO number - you may have more or less than 3 types - edit as needed.)

Then, in your PO Number column (which WILL be visible), create the following column-level formula:

=IF([PO Request]@row = 1, [Auto Number]@row, "n/a")

(NOTE: you can also just leave the rows that don't need a PO Number blank by replacing "n/a" with "" in your formula.)

Note that the one disadvantage here is that your PO Numbers will have gaps because some will not be used.

Hope this is helpful! Please feel free to tag me in a follow-up post if not, and I'm glad to help. ☺️

Arianna Sikorski

@Danielle Arteaga Thank you for the ideas! I had thought of the same thing, but I was hoping to find a formula that could prevent the gap between PO numbers so no one is wondering "Where's PO-0014?" when it actually doesn't exist. Would there be a formula that could take the number copied above and count it? So if it doesn't say "n/a" then it can look at the previous entry and count up from there? This way there are no gaps in the PO numbers. Not sure if that's possible.

Danielle Arteaga

Hi, @Arianna Sikorski

Would this work for you? I changed the Auto Number column to remove the "PO" suffix. Now you have only numbers:

Then, I changed the formula in the PO Number column to this:

=IF([PO Request]@row = 0, "Non-PO" + "-" + [Auto Number]@row, "PO" + "-" + [Auto Number]@row)

Now, your numbers are serialized, but you can clearly see what generates a PO and what does not. You can also now use the PO Request column to generate a report that shows only the items that actually generated a PO.

It's not a perfect solution to your concern, but it preserves your ability to keep these formulas at the column level so that your sheets do not break as you add new rows. It is possible to do what you're suggesting so that only your actual PO items are numbered, but that would require a formula that would have be continuously dragged down a row and in cells that could be accidentally overwritten. If you want to go that route, let me know.