Conditional Auto Numbers / IDs

Justus

Hi all,

I am pretty new at smartsheet and I am looking for a kind of conditional auto counting. With the on-board-counting function in the column prefs I can't get any further. 

I need different IDs regarding the level of the row. Ideally it should work like the example in the screenshot. Any ideas? I would be very happy for any input, thanks

Justus

 

Paul Newcome

This can be done with the use of formulas. What's the logic behind building the ID?

Justus

On one hand I need unique IDs depending on the level (not every row) on the other hand I must count entries depending on levels. I would be very thankful for short examples ... thx very much

Paul Newcome

Are you able to provide more detail? Are you trying to get it to look EXACTLY like your screenshot in the original post with the I id's in one column and the T id's in another column?

Do you want different letters?

Will there be additional levels? If so, how will those be formatted?

Do you eventually want all id's to be in the same column?

Do you want the Task number to start over at 1 when you start a new Issue level?

Does the screenshot show the exact format you want?

Justus

No, there is no need that they look exactly like in the screenshot. 

No, no more levels for sure. 

All IDs in one column could be nice, but at the end I need to count the amount of entries in tasks and issues separatly - for a kind of overview/dashboard. (total xy issues and xy tasks) 

Tasks numbers always starting at 1 would be great

Screenshot = no, just a really rough example.

At the end I imagine a nomenclature like in a classical outline:

1.

  1.1.

  1.2.

  1.3

2.

  2.1

  2.2

  ...

...

Paul Newcome

Ok. So in the screenshot below, [Column24] shows what you have in your screenshot except combined into one column. [Column26] shows the classic W.B.S., and [Column25] shows a hybrid of the two.

 

Do either of these 3 work for your, or did you want something different? Something different would only require some minor adjustments, so feel free to ask away.

Justus

Cool. Column 26 is it 

Ideally it would be perfect to separate this Column into two: one for 1 / 2 / ... and the other for 1.1 / 1.2 / 1.3 / 2.1 / 2.2 etc.

Is that possible? And what is the code???

Justus

But the WBS layout only in one column as you already made would be also interesting. I do not know yet how I will integrate the IDs ... 

Paul Newcome

Ok. So breaking [Column26] into two columns opens up more options. The screenshot provides two of those options.

.

The formulas are going to vary based on exactly what you want, so I will wait to post those until we have that part figured out.

.

By "integrate the IDs", I assume you mean incorporating the letter to denote Issue vs Task?

.

We're currently tackling the hard part: figuring out exactly what your end goal is. After that it get's much easier because it is simply a matter of piecing together various functions and formulas to achieve the desired outcome.

Justus

ok, I understand. One step back: I figure out with which tool we are building our FMEA (risk management for product devel) in future. We use Google Sheets but I am really not happy with that, because we have to adapt ourselves more to the tool than that the tool adapts to our requirements. 

In an FMEA there are in general two levels:

(1) the risk (issue) and

(2) mitigations (tasks).

Normally there are several mitigations for one risks. For example:

Risk 1: client is too stupid to use functions, is frustrated and gives up using the product

Mitigation 1.1 build up a help center

Mitigation 1.2 build up a nice community

Mitigation 1.3 build up a great team of coder who makes the work for the client ;-)

And now back to my request: meanwhile I was playing with the function and getting a little bit familiar with them. I found out how to count different levels which is important for an overall view/report for progress etc. So your first version would be totally fine for me. 

 

Paul Newcome

Sounds good. The first thing you'll need is a helper column that I typically call "Ancestors". In this column I put a very basic

 

=COUNT(ANCESTORS(Task@row))

 

and dragfill that on down. For your setup, this will give you either a 1 or a 0 depending on your hierarchy level.

Here's where it gets "tricky" (not really).

.

In the FirstA column, you would use the formula of

 

=COUNTIFS(Ancestors$1:Ancestors@row, 0)

 

Note: We put this formula in the first row. Locking in row 1 means the range for the COUNTIFS will always start in the first row and using the @row for the end of the range means the COUNTIFS will stop counting on whatever row the formula is on.

.

For SecondA I will explain it in two parts because that's how I build it.

First we want to count how many times (using the same range as before) the Ancestors column has a 1 in it. We also want to include the criteria of the FirstA column holding the same number (this is what resets the number to 1 at the start of each Issue).

=COUNTIFS(Ancestors$1:Ancestors@row, 1, FirstA$1:FirstA@row, FirstA@row)

.

This is what generates the numbers.For cleanliness, I like to remove the zeroes, so I use an IF statement to say that if that calculation is greater than zero, run it, otherwise leave the cell blank.

 

=IF(COUNTIFS(........) > 0, COUNTIFS(.........))

.

So that gives us:

=IF(COUNTIFS(Ancestors$1:Ancestors@row, 1, FirstA$1:FirstA@row, FirstA@row) > 0, COUNTIFS(Ancestors$1:Ancestors@row, 1, FirstA$1:FirstA@row, FirstA@row))

.

There are also a few options for combining the two columns if you wanted to do that. The easiest way being to just use a JOIN/COLLECT to join the two columns together wherever a number exists (the COLLECT function means that you won't have extra delimiters floating around on your first level.

 

=JOIN(COLLECT(FirstA@row:SecondA@row, FirstA@row:SecondA@row, @cell <> ""), ".")

.

Incorporating Identifiers can actually be a pretty straightforward task as well if you were wanting to add some kind of note such as client name or something of the sort.