relative address or reference

  • Thread starter Thread starter James C.
  • Start date Start date
J

James C.

Hi, I am having problems trying to dynamically link files to my main files.

In my Main file I have a cell that is linked to another workbook. For
simplistic sakes lets say cell A1. The formula in A1 is
='H:\Files\[073109.xlsx]Backup'!$A$1.

- So no problem here. Whatever is in my 073109.xlsx file on the Backup tab
in cell A1 will populate my other A1 cell.

I want to make my main file dynamic so that I can change the reference file
on the fly. To do this I put in a variable cell in B1 in my main file that
the user can input the date of the file.

- In A1, I now put in the formula
=concatenate("'","H:\Files\[",B1,".xlsx]Backup'!$A$1")... if I put 073109
into B1 then I get the following in cell A1
"='H:\Files\[073109.xlsx]Backup'!$A$1"

The problem is that it just shows the text instead of actually retrieving
the data in that file. I need it to show the result not just provide the
string?

Any ideas?
 
=concatenate("'","H:\Files\[",B1,".xlsx]Backup'!$A$1")
if I put 073109 into B1 then I get the following in cell A1
"='H:\Files\[073109.xlsx]Backup'!$A$1"
Click to expand...

The probleem with that is the result of the CONCATENATE function is a TEXT
string even though it looks like a formula.

If you only have that one cell that links (probably not, though) you can
convert the TEXT string to a formula by:

Select the cell in question
Edit>Copy
Then, Edit>Paste Special>Values>OK
Then, hit function key F2 then hit ENTER

If you have a lot cells that link...

Select the range of cells in question
Edit>Copy
Then, Edit>Paste Special>Values>OK
Then, Edit>Replace
Find what: =
Replace with: =
Replace All

If you want it to be truly dynamic you could use this formula:

=INDIRECT("["&B1&".xlsx]Backup!A1")

However, this *requires* that the source file *must* be open (which is
usually undesirable!).

A possible alternative is to download and install the free add-in
Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

It contains a function which might work. The function is called
INDIRECT.EXT. It works just like the built-in INDIRECT function except the
source file doesn't need to be open. Since the source file doesn't need to
be open you'd need to include the full path to the file.

=INDIRECT.EXT("'H:\Files\["&B1&".xlsx]Backup'!A1")

--
Biff
Microsoft Excel MVP


James C. said:
Hi, I am having problems trying to dynamically link files to my main
files.

In my Main file I have a cell that is linked to another workbook. For
simplistic sakes lets say cell A1. The formula in A1 is
='H:\Files\[073109.xlsx]Backup'!$A$1.

- So no problem here. Whatever is in my 073109.xlsx file on the Backup tab
in cell A1 will populate my other A1 cell.

I want to make my main file dynamic so that I can change the reference
file
on the fly. To do this I put in a variable cell in B1 in my main file that
the user can input the date of the file.

- In A1, I now put in the formula
=concatenate("'","H:\Files\[",B1,".xlsx]Backup'!$A$1")... if I put 073109
into B1 then I get the following in cell A1
"='H:\Files\[073109.xlsx]Backup'!$A$1"

The problem is that it just shows the text instead of actually retrieving
the data in that file. I need it to show the result not just provide the
string?

Any ideas?
Click to expand...
 
Back
Top