can shorten this formula?

[mingali]

Hi,

In my worksheet, i have formulae as follow:

IF(I$5="actual",IF(ISERROR(VLOOKUP($A17,'[worksheet1.xls]fun'!$A:$AO,I$3,FALSE)),0,VLOOKUP($A17,'[worksheet1.xls]fun'!$A:$AO,I$3,FALSE)),0)

FYI, I$3 is a blank cell.

As there are so many helpful experts on this forum, i would like to know is it possible to shorten this formula?

Thanks

[DonkeyOte]

If the source file is not always open certain formulae are not viable, specifically SUMIF - I say SUMIF because if A17 appears only once in Col A of the other file and the value being returned from the matrix is numeric you could use SUMIF which would by default return 0 if there were no match, eg:

Please Login or Register  to view this content.

If the value is always numeric you could use:

Please Login or Register  to view this content.

which though not shorter in length does not evaluate the VLOOKUP twice.

(I'm assuming you're generating some sort of matrix with codes on left and months across the top)

[mingali]

Hi DonkeyOte,

Your assumption is correct. However, may i know which formula take up less memory to run? my firm has too many excel files with formula and linking and it takes up a lot of RAM and slow to open. Some files are so large that it takes a few minutes to open, especially when source files require to be opened also.

Thanks

[martindwilson]

you might want to change those whole range references
$A:$AO, to a reasonable limit
say A1:AO1000
or somwhere around the max data is likely to be

[DonkeyOte]

Hi DonkeyOte,

Your assumption is correct. However, may i know which formula take up less memory to run? my firm has too many excel files with formula and linking and it takes up a lot of RAM and slow to open. Some files are so large that it takes a few minutes to open, especially when source files require to be opened also.

Thanks

If the Workbooks are closed then I would presume the LOOKUP based approach would perform better than the double evaluation method, that said I don't have the requisite knowledge to state that with any real conviction.

As a general rule (and as impractical as it may sound) I have always gone to great lengths so as to avoid linking files as the process was far too "fragile" for my liking, not to mention the issues that arise if you travel around to any great extent
ie having perm links to files in the UK when travelling in the US is not very practical!

A central repository is nearly always the best approach be it a purpose built database like SQLSvr, MySQL etc or even an XL file ... essentially trying to retrieve info link-less into the file in which the calculations are to be performed

There are a few people out there who regularly use ADO to retrieve info from closed XL files, see for ex: http://www.rondebruin.nl/ado.htm