Alternative for Indirect Formula

[RadheyaMansel]

Hi,


I'm building a stock management system, and I need a formula that looks through a range of codes on each worksheet in a workbook. It adds the number in a specified column on the same row, and brings the sum of these numbers back to a summary page. Basically it adds up total usage of each type of ingredient. I have this formula, and it works really really well:

=SUMPRODUCT(SUMIF(INDIRECT("'"&ShtLst&"'!C20:C65"),$AM48,INDIRECT("'"&ShtLst&"'!V20:V65")))

...Only problem being that it slows excel down to a crawl when used frequently.


Would anyone have an alternative to this formula that doesn't make excel so slow?

Thanks guys,
Radheya

 

[Aladin Akyurek]

See http://decisionmodels.com/calcsecretsi.htm for INDRECT and kindred functions.

A solution would be organize all of the data in one sheet in a tabular form. Such data can be processed faster, i.e. more efficiently.

 

[RadheyaMansel]

I have a lot of information in separate sheets, to transfer it all to one sheet would be a bit of a pain.

I'm looking for a formula that does the above but is not so volatile

 

[Aladin Akyurek]

I have a lot of information in separate sheets, to transfer it all to one sheet would be a bit of a pain.

I'm looking for a formula that does the above but is not so volatile

My reply implies that there isn't any other formula option. I'm not sure whether a VBA approach would perform better. Let's wait and see.