How would you deal with named external references?

Name: test1
Refers to: ='C:\TV\[test1.xls]Sheet1'!$A$1:$A$5

Formula: =SUM(test1)


--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Mon, 10 Dec 2007 16:53:01 -0800, camexcel
wrote:

In the current workbook I have a formula linked to another spreadsheet.
What
I want to do in the current workbook is to extract the filename only into
a
column to represent the source of the file linked..


A VBA UDF is the easiest way.

The following will extract one or more file names and return them as an
array.
(If there is only one file name, no need to index through the array).

It takes a single cell as the argument; multiple cell ranges will give a
REF
error.

If there is no file name in the cell formula, it will return a blank.

To enter, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in
the Project Explorer window, then Insert/Module and paste the code below
into
the window that opens.

To use it, enter the formula =FN(cell_ref) in some cell.

================================================== ======
Option Explicit
Function FN(rg As Range) As Variant
Dim re As Object, mc As Object
Dim i As Long
Dim Temp()

FN = ""
If rg.Count < 1 Then
FN = CVErr(xlErrRef)
Exit Function
End If

If rg.HasFormula = True Then
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\[([^[]+)]"
If re.test(rg.Formula) = True Then
Set mc = re.Execute(rg.Formula)
ReDim Temp(0 To mc.Count - 1)
For i = 0 To mc.Count - 1
Temp(i) = mc(i).submatches(0)
Next i
FN = Temp
End If
End If
End Function
====================================
--ron