INDIRECT-like UDF

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

ADVERTISEMENT

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

Active Member
Joined
Feb 17, 2002
Messages
345
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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579

ADVERTISEMENT

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

Active Member
Joined
Feb 17, 2002
Messages
345
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

Active Member
Joined
Feb 17, 2002
Messages
345
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,352
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top