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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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,476
Messages
5,548,259
Members
410,825
Latest member
Dave12
Top