Indirect and Sum product Slow

**pauldaddyadams** · 06-20-2012, 07:43 AM

Previously I posted up two threads regarding a formula that could pull data by referencing a sheet name in a cell - I was recommended the following formulas:

=IF(ISBLANK($B6),"",(INDIRECT("'"&$B6&"'!"&E$2)))

=SUMPRODUCT(N(INDIRECT("'"&ROW(INDIRECT("1:420"))&"'!"&CELL("address",P23))))

Both of these formuals appear on different summary sheets and work but are very time consuming

Research I've found out that INDIRECT is 'volative' as it continually calculates every cell and hence the slow down in speed.

Can anyone suggest any other formulas?

**JBeaucaire** · 06-20-2012, 01:20 PM

THE function that let's you construct a reference from pieces of strings is INDIRECT(). That's what its' for. The idea is to use as few of these as possible. Or, keep the number of cells being evaluated to a minimum.

That second formula implies 420 SHEETS. Is that true? I'd consider a reevaluation of your data storage methodology. A single sheet can have a million rows of data. That single sheet can can be referenced, parsed, filtered, extracted from with a myriad of robust functions, robust because it's a single sheet and no magic trick formulas need be employed.

Anyway, simple goal... 1 data source... 1 robust report sheet. That's a master design, IMO.

**pauldaddyadams** · 06-21-2012, 05:05 AM

420 Sheets is true sadly!

I have come to the realisation that I will need to rearrange the stotrage of the data but I havnig trouble doing so (posted here):

HTML Code: