INDIRECT-like UDF

[pilot]

My wb has more than a thousand formulas where I want to use INDIRECT but it is impractical, in some cases because I don't want to open the several hundred files being referenced and in other cases because the files being referenced don't yet exist. Would it be possible to build a UDF that would do a similar function? Specifically, the formulas refer to file names that include dates (mm-yy) as part of the name such as "08-02 Trial Balance.xls". In this example, the serial date for 08-31-02 is on the same line in Col A. I envision a Function that would pick up the date portion of the file name, combine it with the rest of the path and name, and return it in such a way that VLOOKUP or direct reference to a cell in the named file would be possible.

 

[Aladin Akyurek]

INDIRECT is a complex derefencing function... I don't know whether it is available as a UDF.

But GetValue might be of help in order to fetch a cell value from a closed book:

http://makeashorterlink.com/?U447216C1

 

[pilot]

My current formulas are doing what GetValue would do in code, but what I'm trying to avoid is lots of formula editing or Find/Replace activity. I know we've beat this horse before but for some reason, I just can't help thinking there must be a way. Similar to how SETV hold a temporary value in its variable, I was thinking a function could do the same, returning a concatenation using the variable as part of the final string returned, in such a way that VLOOKUP would think it was looking at any other path & file.

 

[Aladin Akyurek]

On 2002-09-13 07:23, pilot wrote:
My current formulas are doing what GetValue would do in code, but what I'm trying to avoid is lots of formula editing or Find/Replace activity. I know we've beat this horse before but for some reason, I just can't help thinking there must be a way. Similar to how SETV hold a temporary value in its variable, I was thinking a function could do the same, returning a concatenation using the variable as part of the final string returned, in such a way that VLOOKUP would think it was looking at any other path & file.

Care to post your current formula along with what modification it needs?

 

[Ian Mac]

If your trying to do a VLOOKUP with the data once you've indirected to it.
SQL.REQUEST sounds exactly what you need,
This is available using ODBC Add in.
SQL.REQUEST is a function that pulls information from data sources using SQL driectly in the Function. It works very well with drop down text strings as both path and Select....

It's fairly complicated (to me anyway) but I have just put together a working model if you'd like a walk through.
I can't provide it today, but if you'd like to try it I can go through it tomorrow.

Kind Regards,

 

[pilot]

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.

 

[Corticus]

Just an idea,

You suggested using concatenation, this could work. If you concatenated cells that contained the dynamic dates you were looking to reference(the .xls files), then use copy and paste-special, paste as values.
If you then find and replace '=' with '=', the new concatenated functions should do there thing. This would be the only find and replace you would have to do and it could(should) be aoutomated.
This is a dirty method of making dynamic links.
I might be totally missing your questions but maybe this helps?

Corticus
This message was edited by Corticus on 2002-09-13 10:07

 

[pilot]

Ian,

The ODBC addin is not currently in my list and I can't add it (networked Excel). Otherwise, sounds like it was a distinct possibility.

 

[pilot]

Corticus,

Your idea sounds like something I would try, knowing as little about VBA as I do. I'll play with it and see what happens. Thanks for all responses.

 

[Aladin Akyurek]

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