Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference file
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)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference file
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: 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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference file
Sorry, I removed the file path because it is very long - will this still work
to reference a full file path? "Stefi" wrote: 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: 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)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference file
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. Miree wrote: Sorry, I removed the file path because it is very long - will this still work to reference a full file path? "Stefi" wrote: 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: 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)) -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference file
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: Sorry, I removed the file path because it is very long - will this still work to reference a full file path? "Stefi" wrote: 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: 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get file reference using File System Object | Excel Programming | |||
Linking to another file using file reference typed cell | Excel Worksheet Functions | |||
cross-reference data from one file to another file | Excel Discussion (Misc queries) | |||
How do I reference external data from a file, file name found in . | Excel Discussion (Misc queries) | |||
copy/paste from one file to another without file name reference | Excel Discussion (Misc queries) |