Increment Auto number column based on check box or other trigger

Paul Newcome

Sorry about that. I got a little ahead of myself. Glad it is working for you now.👍️

Jeana

No worries! Thank YOU!

Paul Newcome

Happy to help. 👍️

Jeana

Hi Paul @Paul Newcome,

They have thrown a twist at me on this sheet. Now they don't want a trigger to increment the Asset #. They just want an incremented asset # to automatically be added when the Indent Level = 1.

Here's what I've modified to do that and it works until I get to the next Indent Level. I think I'm over thinking it.

When the next set of values at Indent Level 1 comes up how do I grab the previous Asset # at Indent Level 1.

Thanks!

Paul Newcome

So "Title 1" would be "26000", each of the child rows under that would be "26001", and then "Title 2" would be "26002"?

Can you provide a screenshot with the correct numbers manually entered along with an explanation of why they are those numbers?

Jeana

Yes Paul and thank you for you quick response. Here's a screenshot of what I want to happen. Please note the new sub steps as well. They do NOT need a different asset number. Only those at Indent Level 1 should get a new number.

THANKS!!

Let me know if that doesn't make sense. :-)

Paul Newcome

Ok. Leaving row 1 blank and starting in row 2, try something like this...

=IF(COUNT(ANCESTORS([Module Name]@row)) = 1, "PL" + 26000 + COUNT(DISTINCT([Helper Asset #]$1:[Helper Asset #]1)), PARENT())

Dragfill this down, and you should be up and running.

EDIT: Missed a closing parenthesis in the formula. Edited to add where needed.

Jeana

Ok, so this is looking at the Ancestors in the Module name and if there is 1 apply the formula for the Asset #. I'm getting this error. Thoughts?

I gave you temporary access to the sheet if that helps. Appreciate your time so much!!

Jeana

Jeana

Well, I would give you access but I guess I would need your email address. Let me know if it would help to do that and I'll add you.

Thanks,

Jeana

Paul Newcome

That is because you pulled the original formula. I made an edit to fix it with a closing parenthesis I had missed. I fixed it almost immediately, so you must have grabbed it pretty quickly. Haha.

Jeana

Guess I'm too guick on the draw this morning.

This worked but it's not incrementing the 26000. Now I just feel stupid. :-/

=IF(COUNT(ANCESTORS([Module Name]@row)) = 1, "PL" + 26000 + COUNT(DISTINCT([Helper Asset #]$1:[Helper Asset #]1)), PARENT())

Paul Newcome

=IF(COUNT(ANCESTORS([Module Name]@row)) = 1, "PL" + 26000 + COUNT(DISTINCT([Helper Asset #]$1:[Helper Asset #]1)), PARENT())

The bold column name should be whatever column you are putting the formula in.

Jeana

I guess I'm not understanding how DISTINCT is going to help to increment the Asset #? I updated the column info to the Asset # column because that's where the formula should be to calculate the next Asset #. It looks like I don't need to provide a starting value because it's in the formula. I don't even need the Helper column anymore, which is great!

Sorry to be so dense about this one. My brain thinks it's Friday!

Paul Newcome

The DISTINCT function basically gathers all of the numbers above the row with the formula and counts one time for each distinct entry.

So PL26000 can be on any number of rows above it but it will only be counted as 1. 1 + 26000 = 26001 which is the next number in line. Then we get down to the next indentation that needs a new number and we have 2 distinct numbers. 26000 and 26001. So the COUNT function returns a 2 since there are two unique numbers and adds it to the base number of 26000 which gives the result of 26001.

Jeana

@Paul Newcome Hi Paul, I had this formula working just fine and somehow it got broken, probably by me. :-/

It's incrementing the Asset # but not at the correct Indent Level. It's currently only incrementing at Indent Level 1.

I need each Indent Level 2 to have a new asset # (incremented by one) . I've tried several things to correct it but I'm stuck.

Can you help Please? The left column is my desired outcome. The red is where I need it to increment.

Thanks for your time!

Jeana