Issues with autogenerated Asset #'s
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:
- 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.
- 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.
Help Article Resources
Check out the Formula Handbook template!