Shorten Formula or UDF for Indirect, Substitute, Mid, Find, Rept

[jbiehl]

Hi Everyone,
I'm looking to shorten the following formula or create a UDF for it. It's in thousands of cells and it seems to be slowing down the entire workbook. I read that the "Indirect" function specifically slows the workbook down, but I couldn't some up with another way to call another worksheet based on the cell header.
In this case, KA$1 is "RL.8.2 September Grades" The find(left is because it's searching for the "RL.8.2". The indirect(left(substitute(mid(find is searching for the worksheet titled "Sep.Grades!" (this first three letters of the 2nd string & ".Grades".

=IFERROR(SUM(IFERROR( ISNUMBER(FIND(LEFT(KA$1,FIND(" ",KA$1)-1),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1")))*XLOOKUP($A2,INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!A:A"),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1048576")),""))/SUM(IFERROR(ISNUMBER(FIND(LEFT(KA$1,FIND(" ",KA$1)-1),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1")))*ISNUMBER(XLOOKUP($A2,INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!A:A"),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1048576")))*LEFT(SUBSTITUTE(MID(INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1"),FIND("s:",INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1")&"s:")+2,9)," ",REPT(" ",9)),9),"")),"")

Any ideas on how to shorten this and/or speed it up would be greatly appreciated.
Thanks!

 

[jbiehl]

Thanks to someone on a separate thread, I'm now down to this:

=IFERROR(SUM(IFERROR( ISNUMBER(FIND(LEFT(KA$1,FIND(" ",KA$1)-1),INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!1:1")))*XLOOKUP($A2,INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!A:A"),INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!1:1048576")),""))/SUM(IFERROR(ISNUMBER(FIND(LEFT(KA$1,FIND(" ",KA$1)-1),INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!1:1")))*ISNUMBER(XLOOKUP($A2,INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!A:A"),INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!1:1048576")))*LEFT(SUBSTITUTE(MID(INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!1:1"),FIND("s:",INDIRECT("'"&MID(KA$1,FIND(" ",KA$1)+1,3)&".Grades"&"'!1:1")&"s:")+2,9)," ",REPT(" ",9)),9),"")),"")

I'm still hoping to get rid of the "Indirect".