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.

 

[NateO]

Howdy, not quite sure what we're up to here, but one can use vba to dynamically update the file link on a calculation. I cooked up the following example:

Book1
	A	B	C	D	E	F
1	08-02	test2				08-02
2						08-03
Sheet1

Basically I used a data validation list in A1 of column F values. When you you change a1 in XL 2000, you'll inspire Excel to recalc. Accordingly I used a worksheet_change procedure to replace the dates in all of the workbooks formulae. The procedure I used is as follows:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro recorded by Nate
Dim Worksheet As Worksheet, cl As Range, frm As String
Dim z As String, o As Integer, s As Integer, FrmCls As Range
If Target.Address = "$A$1" Then
For Each Worksheet In ThisWorkbook.Worksheets
Set FrmCls = Nothing
On Error Resume Next
Set FrmCls = Worksheet.[a1].SpecialCells(xlFormulas)
If FrmCls Is Nothing Then GoTo 1
For Each cl In FrmCls
frm = cl.Formula
If InStr(frm, "[") Then
o = InStr(cl.Formula, "[")
s = InStr(cl.Formula, "]")
z = Mid(cl.Formula, o + 1, s - o)
cl.Replace What:=z, _
Replacement:=Format([a1], "mm-yy") & _
" Trial Balance.xls]", _
MatchCase:=False, lookat:=xlPart
End If
Next cl
1:
Next Worksheet
End If
End Sub</pre>

This has to go in the correct worksheet's module. A quick way to get there is to right-click on your worksheet tab, click view code and paste the code above in.

Perhaps this may be of help. Have a nice weekend.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-09-13 13:47

 

[pilot]

Aladin,
I really like the way you were able to shorten that mess. I had to make one little fix for it to work.

=IF(ISERROR(V(VLOOKUP($A34,File10_12,12,0))),"",V()),"")

became

=IF(ISERROR(V(VLOOKUP($A34,TB10_02,12,0))),"",V())

Short of trying what Corticus suggested (which I haven't had the chance to do), there just doesn't seem to be a way of picking up the date part of the filename by reference. I guess I can live with that.

 

[Aladin Akyurek]

I really like the way you were able to shorten that mess. I had to make one little fix for it to work.

The trailing blank was the result of copying and pasting of your original formula...

Give also a shot on prelisting possible files in the name space (Insert|Name|Define). I hope you don't have too many of them.

 

[Ian Mac]

Hello,

I hope I'm not too late,
I've just realised I should have mentioned that the Add-In I recomended is not with the others, the path is generally:

C:Program FilesMicrosoft OfficeOffice 10LibraryMsquery

you will need the 'Browse' for it in Add-Ins.

If anyone wants more on the idea I've put forward, I'll continue with the 'How To'.