I am looking for a solution to dynamically sort using a "XXX: T_Order" field in a series of date groups in order to calculate dates based on the order in which each activity will take place and the duration of each. My current solution is to search across the current row using the following formula:
AP47 Formula: (Data searched from A47 to CC47)
=IFERROR(IF(AP47="N/A","N/A",WORKDAY(INDIRECT(ADDRESS(ROW(),MATCH("T"&RIGHT(AP47,2)+1,$A47:$CC47,0)-4)),-1,Holidays)),"TBD")
Screenshot: https://ibb.co/jK7QgT
Goal: Starting with an end date, work backwards to calculate the start and end dates of up to 12 activities based on the individual duration (# of days) and sort order (T11-T22, or N/A) to automatically calculate dates.
Logic: Search the entire row for the unique criteria of T11-T22 which are the valid fields for the multiple T_Order fields and identify the next highest value in the row. Based on that location the reference would point to the start date of the next activity so that the end date of the previous activity could be determined.
Problem: The inherit problem with this approach is that I have been unsuccessful in avoiding a circular reference which is obvious given the fact that I am searching the range which contains my formulas.
Workaround: I was able to solve this issue by Enable iterative calculation (Max Iterations: 10); however, this is not a long-term solution and the performance of the sheet is poor. As a shared workbook, users are also encountering issues as they too would need to enable the iterative calc.
Any help or suggestions to solve for this would be greatly appreciated. Thank you.
AP47 Formula: (Data searched from A47 to CC47)
=IFERROR(IF(AP47="N/A","N/A",WORKDAY(INDIRECT(ADDRESS(ROW(),MATCH("T"&RIGHT(AP47,2)+1,$A47:$CC47,0)-4)),-1,Holidays)),"TBD")
Screenshot: https://ibb.co/jK7QgT
Goal: Starting with an end date, work backwards to calculate the start and end dates of up to 12 activities based on the individual duration (# of days) and sort order (T11-T22, or N/A) to automatically calculate dates.
Logic: Search the entire row for the unique criteria of T11-T22 which are the valid fields for the multiple T_Order fields and identify the next highest value in the row. Based on that location the reference would point to the start date of the next activity so that the end date of the previous activity could be determined.
Problem: The inherit problem with this approach is that I have been unsuccessful in avoiding a circular reference which is obvious given the fact that I am searching the range which contains my formulas.
Workaround: I was able to solve this issue by Enable iterative calculation (Max Iterations: 10); however, this is not a long-term solution and the performance of the sheet is poor. As a shared workbook, users are also encountering issues as they too would need to enable the iterative calc.
Any help or suggestions to solve for this would be greatly appreciated. Thank you.
