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

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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:

Some videos you may like

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
Joined
Jul 30, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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".
 

Watch MrExcel Video

Forum statistics

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