Hey guys,

I have 15 named formulas (named ranges that don't refer to any actual cells) in one of my spreadsheets. 10 are 300 by 40 array that is created from a macro which contain financial data and the other 5 contain information in 300 by 1 arrays that are used for identification. For instance, one would be

Income = FullArray(Property) = {1,2,3,4,5...,40; 2,3,4,.....}

Everything works fine and I can use the named formulas exactly as I wanted mainly by using sumproduct. For example,

sumproduct(--(ID_Num=1),--(Type=2),GrabColumn(Income))

Where ID_Num and Type are 300x1 and contain identification data and income contains the financial information. The problem is this is very very slow. I think that each cell is recalculating the named formulas and is really slowing things down. Is there a way to still use this technique and improve performance?

Note: If I enter the data into actual cells by selecting a range of the same size as the arrays and pressing cntl+shift+enter and run the sumproduct on the cells it is very very fast. But the reason for having the named formulas is so that I dont have to have 15 sheets of data.