Looking for some help here - I am trying to reduce the file size of an excel file I use on a quarterly basis. The file was initially 18,000 KB and I've gotten it down to 10,000 KB but I think there is still room to improve.
My file has 145 sheets. 12 of those sheets use the INDIRECT function paired with HLOOKUP to pull data from 100 individual sheets that contain information relating to specific investments over a period of time. Does anyone know if it would be faster (and if my file size would get smaller) if I replace these functions with VBA code? I've only used VBA a few times so I would need specific instructions on how to implement this.
Here's an example of one of the functions:
= IFERROR(HLOOKUP(L$9,INDIRECT("'"&$B18&"'!"&$B$3),14,FALSE)/1000,0)
Where:
My file has 145 sheets. 12 of those sheets use the INDIRECT function paired with HLOOKUP to pull data from 100 individual sheets that contain information relating to specific investments over a period of time. Does anyone know if it would be faster (and if my file size would get smaller) if I replace these functions with VBA code? I've only used VBA a few times so I would need specific instructions on how to implement this.
Here's an example of one of the functions:
= IFERROR(HLOOKUP(L$9,INDIRECT("'"&$B18&"'!"&$B$3),14,FALSE)/1000,0)
Where:
- L9 contains a date that i am looking up in the array on the specific investment tab sheet
- Column B contains the name of the individual tabs (i.e. B18 is "GOX" and i have a separate tab named GOX where i'm pulling data from)
- B3 contains $B$11:$DFR$24, which is the array I am searching on the specific investment tab
- and row 14 is the row I am pulling from the array (so it's technically row 24 in the sheet i'm pulling from, but row 14 from the array i'm looking up within that sheet)