Increment Auto number column based on check box or other trigger

Jeana

I'd like to set up a check box or some other trigger that tells the Auto number column to do its thing. This will NOT always (or usually) be a new row and it will be a row with Children (and it's a Child too). That's why I'm looking for a trigger to increment the auto number.

Can this be done?

Jeana

Paul Newcome

Great. Change the checkbox column to a text/number type and use this...

=COUNT(CHILDREN([Descriptor/Name and Tasks]@row)

Then the number formula would be adjusted to this...

=IF(COUNTIFS(CHILDREN([Child Count Column]@row), @cell <> 0) = 0, "PL" + (26000 + COUNTIFS([Checkbox Column]$1:[Checkbox Column]@row, 1)))

Basically we use that first helper column to output how many children each row has. Then we use a COUNTIFS to count how many of those child rows have a number greater than zero (meaning it too is a parent row making the current row a grandparent row). If that count is equal to zero meaning none of the children have child rows of their own, then we run the number generating formula.

Paul Newcome

You would need to use a formula to generate the row ID instead of an Auto-number column.

Jeana

I thought that might be the answer but I'm still struggling with getting the correct results.

The format of the auto generated number is PL12345 where the next number in sequence would be PL12346. I can pull separate the PL from the digits the digits from the PL but I haven't been able to simply add 1 to generate a new number.

Get digits from PL12345(=UPPER(RIGHT([Auto number]@row,5)

This formula =[Auto number] + 1 just adds the number 1 to end resulting in PL123451

I tried a helper column with 1 in it and using =SUM([Auto number]@row, One@row) with this result - 1

What am I missing?!

Thanks,

Jeana

Paul Newcome

Are you able to provide a screenshot of what you are trying to accomplish with mock data manually entered as a sample?

Jeana

Of course! This will look familiar. :-)

I need a new (incremented by one) Asset # for new Descriptor/Name and Tasks but only if I check a box (not shown) or do something to trigger it. Not all new rows will need an Asset #. The last red box needs to populate with PL25286.

Thanks!!!

Paul Newcome

And what would be the very first number for the sheet?

Jeana

That can be flexible but let's say PL26000.

Paul Newcome

Ok. And will it always be that same level of hierarchy?

Jeana

As far as I know now, yes. But that could change.

Thanks so much for your time on this!

Jeana

Paul Newcome

Let's try this... To account for different hierarchy levels we are going to use a little manual entry (unless we can think of some other way to automate it).

Insert a checkbox column that will be used to check boxes for rows where you want to number to populate.

Then your number column would use a formula along the lines of...

="PL" + (26000 + COUNTIFS([Checkbox Column]$1:[Checkbox Column]@row, 1))

Now for some additional questions to hopefully fully automate it.

Will the rows that have the numbers be the second lowest hierarchy? In other words... Will the child rows of these rows have any child rows of their own?

Jeana

Thanks so much Paul. I'll try this formula and let you know. To answer your question the STEP rows (Children for the rows that need the automated number) will NOT have children of their own.

Thanks,

Jeana

Paul Newcome

So would it be safe to say that any row that is a parent but not a grandparent will need numbered?

Jeana

I think that will work and I see where you're going now. If we can figure that out to totally automate it I think it will work!

Thanks,

Jeana

Paul Newcome

Great. Change the checkbox column to a text/number type and use this...

=COUNT(CHILDREN([Descriptor/Name and Tasks]@row)

Then the number formula would be adjusted to this...

=IF(COUNTIFS(CHILDREN([Child Count Column]@row), @cell <> 0) = 0, "PL" + (26000 + COUNTIFS([Checkbox Column]$1:[Checkbox Column]@row, 1)))

Basically we use that first helper column to output how many children each row has. Then we use a COUNTIFS to count how many of those child rows have a number greater than zero (meaning it too is a parent row making the current row a grandparent row). If that count is equal to zero meaning none of the children have child rows of their own, then we run the number generating formula.

Jeana

Almost there! I made those changes and here are the results. The count of children is working right but when the count is <> 0 it's not incrementing on the parent row for some reason. I don't need or want the number to display for the Children row if that's possible.

Thoughts?

Jeana

Figured it out. I still need the Check box column. Substituted that for the Count Children at the end of the formula and it's working!

Thanks!