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.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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
ABCDEF
108-02test208-02
208-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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

pilot

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

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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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'.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,046
Members
414,116
Latest member
sfullnet

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