I have an excel file where I am trying to link rows with predecessor or successor dependencies (simplified MS project type links)
我有一个excel文件，我试图链接行与前任或后继依赖（简化的MS项目类型链接）
The formulas attempt to automatically calculate the successors from the predecessors. The big challenge was with handling the merged cells in column A. The formula is:
公式尝试自动计算前辈的后继者。最大的挑战是处理A列中合并的单元格。公式为：
{=LARGE(INDIRECT(CONCATENATE("$a$1:$a$",SMALL(IF($C$2:$C$101=INDIRECT(CONCATENATE("a",LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1))),ROW(A$2:A$101)),ROW(A2)-LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)+1))),1)}
As you can see, this calculates the values correctly, but if I add iferror() to handle the error conditions, Excel complains that the function causes it to run out of resources. Is there any way to get around this or to simplify my formula to avoid the issue?
正如您所看到的，这会正确计算值，但如果我添加iferror（）来处理错误条件，Excel会抱怨该函数会导致资源耗尽。有没有办法绕过这个或简化我的公式以避免这个问题？
1 个解决方案
#1
0
It works if we replace all the INDIRECT(CONCATENATE()) references with the non volatile INDEX:
如果我们用非易失性INDEX替换所有的INDIRECT（CONCATENATE（））引用，它就有效：
=IFERROR(LARGE($A$1:INDEX(A:A,SMALL(IF($C$2:$C$101=INDEX(A:A,LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)),ROW(A$2:A$101)),ROW(A2)-LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)+1)),1),"")
#1
0
It works if we replace all the INDIRECT(CONCATENATE()) references with the non volatile INDEX:
如果我们用非易失性INDEX替换所有的INDIRECT（CONCATENATE（））引用，它就有效：
=IFERROR(LARGE($A$1:INDEX(A:A,SMALL(IF($C$2:$C$101=INDEX(A:A,LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)),ROW(A$2:A$101)),ROW(A2)-LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)+1)),1),"")