Issues with autogenerated Asset #'s

Jeana

I am autogenerating an Asset # that combines a Product code with a number. Here is the formula I'm currently using (thanks to Community members who helped last year) and a screenshot of some sample data.

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

The formula is in the Asset # column and must begin with the first Asset Name (in red below). As you can see the formula requires that the previous row (6 in this case) be manually entered to start the count with the DISTINCT function. This isn't a big problem although the Project Managers have to include this in their process. This method has worked fine for some time now but it's no longer sustainable.

Here are the issues I am currently addressing:

1. When an asset is ADDED or is MOVED the formula breaks (#Circular Reference or #Blocked) because the formula in the ADDED or MOVED rows doesn't update the row change in the DISTINCT function.
2. The current formula for the Asset# includes the Product code being entered from a drop down and sometimes there can be two different Product Codes used in the same sheet. Other times we will use the same Product code in multiple sheets. This can cause duplicates using the current method. How can I keep Asset#'s between sheets unique for all Product codes? Example with current method: Project A could have a MG100 Asset # in one sheet and as well as in another sheet where Product MG is being revised.

There may be other issues I'm not thinking of as this data is pulled into a Calendar view, Dashboards and used in various reports. Any insight would be helpful.

Thanks,

Jeana

Jaykel Torres

Hey @Jeana,

To address the first issue, as long as there are two iterations of the Formula above or below a newly added Row, it should update the Range within the Formula accordingly. In this case, only the ending Range value will be changed as the starting value has an absolute reference ($). You may want to try copying the Cell with the Formula (not copying the formula text) and pasting it as this will update the Range as well.

If you are moving a Row that currently is part of a Range within a Formula, it is expected behavior that you may see some CIRCULAR REFERENCE or BLOCKED errors. At this time, there currently is not a way to prevent this from happening other than changing the Range in the formula(s) before moving the Row.

As for the second issue, I recommend changing your Formula slightly for each Sheet to ensure there are no duplicate Asset #s. For example you can change "(Product@row) + 10 " to "(Product@row) + 20" or "(Product@row) + 30".

I hope this helps!

Jaykel

Jeana

@Jaykel Torres

Thanks for your insight. I haven't actually implemented it because we went with another solution that addressed other issues as well.

We created an Asset Catalog for a single source of truth for Assets and this is where we generate the Asset numbers now. Then we feed the asset into the Schedule for production by using Index/Match to pull of the Asset info from the Asset Catalog.

Thanks again for your response!

Jeana