Vba Auto Numbering

[Yiraika Daimaru]

Thesenumbers are updated when you sort them with your data. The sequence might be interrupted if you add, move, or delete rows. You can manually update the numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range.

If you are using the ROW function, and you want the numbers to be inserted automatically as you add new rows of data, turn that range of data into an Excel table. All rows that are added at the end of the table are numbered in sequence. For more information, see Create or delete an Excel table in a worksheet.

So far, I am not seeing how to do this (or if it can be done). I realize that sharing the autonumber will still create unique IDs for each order, but part of the problem we are trying to solve is for people out in the field to know that they have their own set of numbers and if they need to place an order with their vendor but are not near the computer, then just have to remember the last available number in sequence to give a PO # to the vendor. If everyone shares, then you never really know what the next available number is since anyone could take it.

Instead of using a count field in the [Users] table, use a rollup of the MAX(values) of the numbering field. If you use a count field and someone deletes a record, the count will be off and you will end up repeating a number.

I would put the padding of leading zeros in the formula for the unique ID that combines the numbering field with the user name. That way the numbering field can be a numeric field and take advantage of the MAX(values) feature.

I have seen a few posts on this forum from years back which have requested this feature, with confirmation from Smartsheet employees that this was in the works ( -numbering), so it I find it surprising that this still hasn't been done.

In my humble opinion, this solution doesn't work, the numbers are NOT what I would expect from a WBS. EDIT!!! After posting this I went back to the sheet where I took the above screen grab and it had saved and amended all the WBS code to a code that WAS Correct!! Weird that it saved like this first though!

APOLOGIES - I noticed my screen shot above has incorrect Level4 within the numeric only code, that is due to me only coding up to level 3 in that column! I only developed this as an exercise for myself! I went up to Level 4 in the Alpha and level 3 in the numeric. Have adapted the IF to state no level 4!

@Debbie Sawyer, thank you also for the screenshots above, I agree that the solution in the thread shared by Andre does not match what I would expect from a WBS. I will take you up on your kind offer for a sample of the sheet you're currently using, as it looks to be exactly what I was hoping for.

as helpful as the workarounds shown here are, I'm really wondering if this isn't a function that Smartsheet can integrate? It's been talked about for a couple of years already! We are using one of the workarounds on a 1000-line long sheet and it's making it extremely slow in updating the formulas...

@Debbie Sawyer I found this thread in Smartsheet community and was wondering if you still had the sample sheet available to share? I am trying to create something similar and would like to incorporate letters as you have shown here. Thank you.

WBS is just a name for listing your tasks out in a sheet. It is the Work Breakdown Structure. i.e. you are structuring your work to allow you to track its progress. This thread is detailing how the rows are numbered automatically to allow you to reference tasks within the sheet.

You are most probably already implementing a WBS in your Project Sheet without realising it! If you have something like a phase heading followed by all the tasks that you need to complete with that phase of work, then you indeed have a work breakdown structure in your sheet.

I opened a support ticket, but I can't close this pop-up no matter what I click or what browser I use and I'm wondering if anyone has any insight. I'm LOCKED OUT of my work because of some stupid UI refresh alert. ?

I have a sheet that is being used to track decisions. We have a parent row (blue) and then child rows (white) for each group that needs to make a decision. We want the parent row Decision IDs to be sequential numbers "EHR###". The problem I'm running into is that each child row gets an AutoNum as well so as new decisions are added we're actually skipping a lot of numbers at the parent level. Below the parent EHR74 has children that were assigned EHR92 - 95. The formulas from the WBS posted on this forum correct the numbering in our Decision ID column but we're wasting a lot of numbers.

Does anyone know how I can get the parent row to be the only one actually using our desired "EHR###" auto number but also still get the WBS to function so child rows inherit the parent auto number + .#?

This is a problem because we're storing related documents on Google Drive and have an automated process to create folders by the Decision ID. Skipping numbers means we have a ton of empty folders that will never be used and just junk of the drive.

I figured out a work around to solve the parent / child numbering issue. I created a new helper sheet, "Decision Intake Sheet", with an AutoNum column configured to number my parent rows as "EHR###". This sheet has only the fields I gather via my intake form and has an automation to move newly added rows to my "In Process Decision" sheet.

On the "In Process Decision" sheet I updated my AutoNum column to be only numerical, without the EHR number. When rows are moved from the intake sheet to the In Process sheet the value in the AutoNum column is placed in the source sheet AutoNum column. Therefore each parent row now has an autonum of "EHR###" but child rows are created with numerical autonums. This allows the WBS formulas to continue to work as designed.

As you know, there isn't currently a way for Auto-Number Columns to discriminate between parent and children rows when performing calculations. When you have a moment, please let our Product team know about your feedback by filling in this form, here. Thank you!

To avoid possible accidental undesired behaviors, I would recommend testing the following in a copy of the sheet instead of the original. The formula that I used below, assumes that there is an "Ancestors" text/number column with the following Column formula =COUNT(ANCESTORS([Decision Description]@row)) and the Auto-Number column (named Row Number) only adds a numeric value based on the order that was added to the sheet. Note that even if there are some jumps on the row numbers after some tasks in-between were deleted, the formula still assigns the correct number to the "Only parent numbered".

I tested your formula and got it to work but there's one problem I failed to mention. When a parent decision is complete I am moving the parent and all child rows to a "Finalized Decision" tracker to archive it. When I tested this I found that I can end up with the same decision id being used over and over since it's based on the range on the "In Process Decision" sheet. I need to find a way to ensure no number is ever used again once it's been assigned on the sheet. Thoughts?

@Julio S. The more I think about this the more I wonder if what I need is a helper sheet that tracks the Decision ID and feeds it back to the "In Process Decisions" sheet. Perhaps there's a way to link the two so that only parent rows get assigned the auto generated number on the helper sheet and then I can use that number to number my child tasks. I'm going to experiment with this today and see if I can figure it out.

I'm trying include a date range with counting the number of applicants within various depts, in certain date ranges, but it's saying incorrect argument set. =COUNTIFS(DISTINCT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], AND(@cell > DATE (2023, 9, 30), @cell

Hi I am using the auto numbering option for board reference codes - however, if an item is deleted by someone, the number sequence moves to the next item, this makes it impossible to use as a reference code.

I was trying to create a sequential number for each item, that stayed with each item - this could then be used as a reference code for that item (I now cant understand the value of the auto-number, other than to count items).

3a8082e126