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

#### jbiehl

##### Board Regular
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!

Last edited by a moderator:

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jbiehl

##### Board Regular
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".

Replies
4
Views
79
Replies
6
Views
142
Replies
1
Views
69
Replies
3
Views
983
Replies
4
Views
106

Threads
1,114,366
Messages
5,547,486
Members
410,797
Latest member
mlfuson