reference file

[Miree]

I have this formula that works fine, is there a way to replace the file to
reference a cell, so that I can just change the file name in one cell rather
thn the many the formula appears in?

=IF(FILE'!$C4="","",'FILE'!$C4))

 

[Stefi]

In your example FILE is a SHEET name rather than a file name, isn'it? If so,
sheet name being in A1
=INDIRECT(A1&"!C4")

Regards,
Stefi

„Miree†ezt írta:

 

[Miree]

Sorry, I removed the file path because it is very long - will this still work
to reference a full file path?

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

 

[Stefi]

Unfortunately not, in this case use =INDIRECT("["&A1&".xls]sheetname!C4")
but it works only if the referenced file is open.

If you want to change full path of a link then you need some VBA code like
this:

For testing I made a Data validation list for cell A1 containing possible
full paths.
E.g.
C:\work\int1.xls
C:\work\int2.xls
both should contain a sheet named sheet1.
1. Enter ='C:\work\[int1.xls]sheet1'!$C$4 in the result cell
2. Install this event macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
ActiveWorkbook.ChangeLink Name:=alinks(1), NewName:= _
Target, Type:=xlExcelLinks
End If
End Sub

Watch the reference in the result cell when selecting from drop down list of
A1.

Stefi


„Miree†ezt írta: