G
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.
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.