On 2002-09-13 09:57, pilot wrote:
Thanks for tackling this. Here are two examples from different sheets. FileExists is a UDF returning True or False
A13 has serial date for 10/31/2002
T13 =FileExists("P:PersonalPerf plan10-02 Sales logs.xls")
C13 =IF($T13,'P:PersonalPerf plan[10-02 Sales logs.xls]Totals'!C$14,"")
---------------------------------
N1 has serial date for 10/31/2002
N34 =IF(FileExists("G:Zones10-02 ME Investor Trial Balance.xls"),IF(NOT(ISNA(VLOOKUP($A34,'G:Zones[10-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,FALSE))),VLOOKUP($A34,'G:Zones[10-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,FALSE),""),"")
In both examples, I would like "10-02" to be replaced with a UDF based INDIRECT-like reference to the cell containing the date. I obviously can't use INDIRECT here since the trial balance file for 10-02 does not yet exist.
When you dynamically construct part of the filename, then you need to use INDIRECT (or EVAL from Morefunc). And such usage requires the target file to be open.
One option that I think of is to have a preset list of files even if they don't yet exist. You can list them all in the name space like File10_12, etc. Check for their existence with your UDF and do the lookup if appropriate.
BTW, I thrust you'll replace all that double computing with formulas using the V udf:
=IF(FileExists("G:Zones10-02 ME Investor Trial Balance.xls"),IF(ISNA(v(VLOOKUP($A34,'G:Zones[10-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500,12,0))),"",V()),"")
If you could define File10_12 as
='G:Zones[10-02 ME Investor Trial Balance.xls]Trial Balance Report For'!$A$11:$L$500
the above can be further shortened to:
=IF(
ISERROR(V(VLOOKUP($A34,File10_12,12,0))),"",V())
Notice that I used ISERROR instead of ISNA in order to trap the name error that we would get if the target file does not exist yet. This eliminates FileExists.
EDIT: Trailing "" (a result of copying and pasting) omitted.
This message was edited by Aladin Akyurek on 2002-09-13 16:09