Brain storming on how to optimize a formula

[Gord]

Hello fellow excel enthusiasts!
We have a workbook here with this ugly formula in thousands of cells
in many sheets. I want to know if anyone has any ideas on how I can
optimize it because it is extremely slow when recalculating. I will
paste it below (I have separated it into lines and done some
indentation based on logic for ease of reading).


=IF(G$8="ACTUAL",
IF(ISERROR(VLOOKUP($D33,SCDCM,MATCH(G$7,SCDCMHdr,0),FALSE)),
0,
VLOOKUP($D33,SCDCM,MATCH(G$7,SCDCMHdr,0),FALSE)
),

IF(ISERROR(VLOOKUP($D33,Forecast,MATCH(G$7,ForecastHdr,0),FALSE)),
0,
VLOOKUP($D33,Forecast,MATCH(G$7,ForecastHdr,0),FALSE)
)
)


From a programmer's standpoint, I can immediately see one flaw.
Provided the VLookup doesn't procude an error, it is going to be
executed
TWICE! Once for the error check, and once to show the result if there
is
no error.

My first idea was to make a user defined function/formula in VBA, but
unfortunately, those ranges used in the Match and VLookup functions
are defined names representing external ranges in closed workbooks,
and
all my research and questions to VBA forums suggests that you cannot
access external ranges in closed workbooks from VBA.

Here is one such discussion:
http://www.developersdex.com/vb/message.asp?r=3227523&p=2677

I'm now open to just about any idea that anyone might have.
Thanks,

Gord.

 

[Charles Williams]

Hi Gord,

You certainly have a lot of duplication there: first step is to eliminate
the duplication by doing each thing once in some separate cell(s).
There also a number of tips on optimising Lookups at
http://www.decisionModels.com/optspeede.htm


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com