# EXCEL File Reference



## Watsonson (May 24, 2005)

How can I use a cell content to refer to a specific file. I want to change every month a part of the filename in a spreadsheet which has links to the new file name. I don't understand how to insert the reference such that I make changes to one cell and it automatically changes the file name without changing the actual row and column references.


----------



## Zack Barresse (Jul 25, 2004)

Hi there, welcome to the board!!

Take a look at the INDIRECT function. Post back if you need more help.


----------



## Watsonson (May 24, 2005)

firefytr -INDIRECT function requires cell reference; I only want to change the name of the file to be referenced in the link. For example, for August the name of the file being linked would include "Aug" however come September the file name will have "Sep" as part of it.


----------



## Zack Barresse (Jul 25, 2004)

Care to post an example? It sounds like you do need that function. An example would help though.


----------



## Watsonson (May 24, 2005)

firefytr - So I have a couple hundred linked cells typically showing links such as in the examples below:

=''\\reports\share\dept\Department\Common\Data\Spain\[Jul-06_All_SO_Spain.xls]Daily PANS - SCR'!I34
=SUM('\\reports\share\dept\Department\Common\Data\Spain\LocationR\[Jul-06_All_SO_LocationR.xls]Daily Data - MontM'!E17)+'\\reports\share\dept\Department\Common\Data\Spain\LocationR\[Jul-06_All_SO_LocationR.xls]Daily Data - MaxC'!E17

I want to be able to change the "Jul" part of the file name every month without using search and replace. Can i do this by using an Excel function that refers to a specific cell which I would change every month.


----------



## Zack Barresse (Jul 25, 2004)

Well, let's say you have the date in cell A3 with a custom format of *mmm-yy* (to give you Jul-06 or Nov-07) then you could try this indirect ...


```
=INDIRECT("'\\reports\share\dept\Department\Common\Data\Spain\LocationR\["&a3&"_All_SO_LocationR.xls]Daily Data - MontM'!E17")+INDIRECT("'\\reports\share\dept\Department\Common\Data\Spain\LocationR\["&a3&"_All_SO_LocationR.xls]Daily Data - MaxC'!E17")
```
Although I would break them down into individual cells ..


```
=INDIRECT("'\\reports\share\dept\Department\Common\Data\Spain\LocationR\["&A3&"_All_SO_LocationR.xls]Daily Data - MontM'!E17")

=INDIRECT("'\\reports\share\dept\Department\Common\Data\Spain\LocationR\["&A3&"_All_SO_LocationR.xls]Daily Data - MaxC'!E17")
```
This will make it easier for troubleshooting any potential errors. Does this help?


----------



## Watsonson (May 24, 2005)

Firefytr - I have been trying to make it work as you suggested and only now I realise it does indeed work but the other file must be opened at the same time. 
How can I make it happen without the other file being opened at the same time.


----------



## Zack Barresse (Jul 25, 2004)

Watsonson, this is an aged old question that just plain doesn't work very well with Excel. Of course there are ways to do it, but nothing that ends up very good. Most of the time it ends up eating up a lot of resources. Here are some things you might be able to try..

Extract value from closed file:
http://vbaexpress.com/kb/getarticle.php?kb_id=286

Rembo's second post here:
http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1143012534

INDIRECT.EXT function by Laurent Longre (only works for one calculation):
http://longre.free.fr/english

Harlan Grove's Pull() User Defined Function (UDF) - uses VBA:
http://tinyurl.com/k93ed

Daily Dose of Excel covers this issue:
http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

John Walkenbach's UDF:
http://www.j-walk.com/ss/excel/tips/tip82.htm

A good overview:
http://www.mrexcel.com/board2/viewtopic.php?t=70457&highlight=

As you can tell, this is a desired capability, but definitely not an easy one to accomplish. Many people have gone to great lengths to acquire data from closed workbooks. The bottom line is it's not very efficient. It is tough sometimes, but the best way to avoid this is good workbook structuring. Another avenue is, if you don't need them updated everytime, is to have a button (or something to fire a macro with) and have a macro get the value from the closed workbook one time. This would basically be like the other VBA methods, but only fire when you commanded it to. Unless you fired it every calculation, it'd save some speed there.

HTH


----------



## Watsonson (May 24, 2005)

firefytr - Thank you for your help. Although the solution was not as direct as I wanted you have given me some good ideas. I guess we can close this thread


----------



## Zack Barresse (Jul 25, 2004)

Watsonson, I know it's probably not what you imagined, but it's the best we can do with Excel currently. If this works enough for you, don't forget to mark your thread as SOLVED by going to Thread Tools | Mark Solved | Perform Action.

Take care.


----------

