Hello, hoping to find some direction to optimize formulas. The workbook has about 15 sheets all interconnected (I'm not sure how to make a sample section to post here without wrecking it all).
This is for work; it's a system to drop in a footage takeoff from an estimating program in construction, which the excel then reads the codes and aligns them with assemblies which are built from a materials list.
It was working fine until I took the equation in the columns Mat.1 & Mat.1 Value through to Mat.12. and filled down 500 rows.
In the picture, the # next to "Levels" is the floor level pulled from another page (there's 84 levels in the template). There's VBA programming that will filter through the 'master!' sheet on which level is selected and then hide the rows in this sheet that are not relevant to that floor. Now that I've filled down, the VBA looks like it's lagging very shortly after it starts. Now takes about 45 seconds to finish (instead of the 2 seconds it did when I only had these equations in a couple dozen rows).
There's 12 Mat(erial) columns & 12 Value columns, filled down 500 rows:
excelhelp1.jpg
Mat.1:
=IF(INDIRECT("'Master'!"&A$1&(ROW()))="","",IFERROR(IF(INDEX(Assemblies!$I$2:$I$500,MATCH($E4,Assemblies!$C$2:$C$500,0))=0,"",INDEX(Assemblies!$I$2:$I$500,MATCH($E4,Assemblies!$C$2:$C$500,0))),""))
Mat.1.Value:
=IFERROR(IF($C4="Dimension",INDEX(Assemblies!$E$2:$E$500, MATCH($E4,Assemblies!$C$2:$C$500,0)),INDEX(Assemblies!$L$2:$L$500, MATCH($E4,Assemblies!$C$2:$C$500,0))),"")
The above equations reference another sheet to which has an equation just as sad... and so on.
Upon changing the level, the VBA is supposed to (and works with less rows with equations) simply hide the rows that don't have anything in all of the Mat.1 through Mat.12 columns.
This image is about 10 seconds in:
excelhelp2.jpg
When it's finally finished, it's left a pile of rows that have blank lines all over; seems quite random as it's not the same every time. If it didn't mess up and leave the blank rows, I might consider living with the poor equations on my part; but she doesn't work properly.
Any direction would be grand; or help to fix my sad equations.
Edit: perhaps it's the VBA that's assisting in the slowness (runs when the Level is changed):
Bookmarks