Formula renumbers when row added - I don't want it to.

Jeana

I have a formula that populates the Asset #

=IF(COUNT(ANCESTORS([Deliverables and Tasks]@row)) = 2, (Product@row) + 10 + COUNT(DISTINCT([Asset #]$1:[Asset #]6)), PARENT())

This generates MA100, MA101, etc. where each Ancestor = 2 (basically)

This works fine EXCEPT when rows are added in between tasks. Then it renumbers the following Asset #'s. I want the inserted row to have an asset number to not to change the other asset numbers in rows following.

Any suggestions on how to implement this?

Thanks,

Genevieve P.

Hi @Jeana

In testing your formula, it looks like the reason for this is how you have your range set up [Asset #]$1:[Asset #]6

This means that whenever you insert a new row, it will only ever re-do the COUNT of the DISTINCT values from above that row, which re-numbers that cell and the ones below it (as you've found). It doesn't check any rows after where the formula has been placed to find a different COUNT.

You could potentially add in an Auto-Number column to create your numbering system (which would then number an inserted row as a new, next number), but this wouldn't take into account if the data is Distinct or not, so the Parent and Grandparent rows would also have numbers.

I don't actually have a different suggestion other than what you already have implemented, it seems to be a pretty good formula for what you're looking to do!

Cheers,

Genevieve

Jeana

Genevieve,

Thank for your reply and insight. You're right about the Auto-Number column option but like you said it doesn't address the Children, etc. I"m wondering a different work flow would help with this? Maybe generating the Asset #'s based on conditions in another sheet and then pulling the number into the Project sheet. Might be more work than it's worth but it's a thought. If you have any ideas around this workflow I'm all ears!

Thank you very much!

Jeana

Genevieve P.

Hi Jeana,

Yes, that's an idea! You could potentially set up a certain copy-row automation that copies a new row over to a helper sheet. Then this sheet could create the unique Asset Number, without any of the Parent or Grandparent rows. Then you can use some sort of INDEX(MATCH to bring that Asset Number back into the original sheet, to the corresponding child row. It does mean that each of your child rows would need something unique to be able to match across both sheets - do you think that could work?

Jeana

Yes, and thanks for the workflow concept. I will put this on my FUN list of things to try soon. I'll keep you posted.

Thanks!