How to use cell content to reference external filenames

  • Thread starter Thread starter ronnielim
  • Start date Start date
R

ronnielim

I am trying to get the contents of a cell to update the links of a
referenced file.

Let's say I am referencing the following file and cell:

='C:\[*Jan2004*-Expenses.xls]Sheet1'!$A$3

and I have a cell A1 which contains:
JAN2004

How do I get cell A1 to change the referenced filename?
Example:
I key in *Feb2004* in cell A1, and the referenced formula changes to:
='C:\[*Feb2004*-Expenses.xls]Sheet1'!$A$3

Thanks!
 
Hi ronnielim!

If the workbook is opened then you can use:

=INDIRECT("'["&A1&"]Sheet1'!$A$1")

But if the workbook is closed then you will have to use a function
developed last year by Harlan Grove:

See:
http://www.google.com/[email protected]


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Wow that was quick. Thanks for the info.

Yeah I sorta poked around and figured out that INDIRECT needs the
referenced file to be open.
I tried out Harlan Grove's method, but got a #NAME? error

This is what I'm using:
=pull("'"&I2&I5&"Sheet1'!$A$3")

where I2 is the path:
c:\filepath\very long folder names\that folder\

and I5 is the filename:
[Jan2004.xls]

Is the syntax wrong?
As for Harlan Grove's code, I pasted it under Tools>Macro>Visual Basic
Editor>This Workbook window
Is that right?
 
Hi Ronnie!

It should go in an ordinary module.

Select the workbook you want it in in the Project Explorer and then
use:

Insert > Module

You should be able to cut and paste from the ThisWorkbook to the new
module.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Sigh, it's one problem after another.

I've populate a whole table of cells in my -calculation- spreadsheet
with the pull() function. They work fine when I first key/edit them
in.

BUT, the cells with the don't autorefresh after I've updated the other
-data input- file, even if I reopen the -calculation- file.

I have to edit (F2) each cell, press enter without changing the formula
- only then does it refresh.

It doesn't prompt me to Update links when I open the file (I'm guessing
that this is because it's VBA code rather than a native Excel
formula).
Is there a way to get the pull() function to update on fileopen?
 
ronnielim > said:
Sigh, it's one problem after another.

I've populate a whole table of cells in my -calculation- spreadsheet
with the pull() function. They work fine when I first key/edit them
in.

BUT, the cells with the don't autorefresh after I've updated the other
-data input- file, even if I reopen the -calculation- file.

I have to edit (F2) each cell, press enter without changing the formula
- only then does it refresh.

It doesn't prompt me to Update links when I open the file (I'm guessing
that this is because it's VBA code rather than a native Excel
formula).
Is there a way to get the pull() function to update on fileopen?

I'm not sure why you have used pull(). What's wrong with simple (i.e.
'native Excel') references to cells in the data input file?
 
Well, I want to enable the spreadsheet to refer to different files in
different paths, which can be specified by the user.

It's fine to use 'native Excel' references for a few cells, but
I have a table of 80 cells (possibly more in future versions). The
template I'm building is for monthly calculations (so the input files
are also different every month).

Well if there's a better way to do, I'll try it out.

At the moment, the pull() function seems to refresh if I do a
edit+enter on a cell affecting a variable (eg. filename, path), but if
there's a way to do it on file open, please let me know.
Thanks :)
 
Hi Ronnielim!

You might try:

Application.Volatile as the first line after the declaration in the
UDF

But if it doesn't work remove it.

It will suck performance out of your workbook as these cells will
recalculate after every change.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Make sure that calculation is turned on.

F9 Calculates all sheets in all open workbooks.

Ctrl+Alt+F9 Recalculates all cells on all worksheets in all open workbooks.

The latter one will recalculate everything so if you can wait to
run this shortcut yourself it might save a lot of recalculation in
normal usage over choosing to use volatile instead.



Norman Harker said:
Hi Ronnielim!

You might try:

Application.Volatile as the first line after the declaration in the
UDF

But if it doesn't work remove it.

It will suck performance out of your workbook as these cells will
recalculate after every change.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
...
...
I've populate a whole table of cells in my -calculation- spreadsheet
with the pull() function. They work fine when I first key/edit them
in.

BUT, the cells with the don't autorefresh after I've updated the other
-data input- file, even if I reopen the -calculation- file.

I have to edit (F2) each cell, press enter without changing the formula
- only then does it refresh.

Holding down [Ctrl] and [Alt] keys, then press [F9]. That works for me, but if
it doesn't for you, then select the cells contain the pull() function and run
Edit > Replace, replacing = with =. That has the same effect as hitting [F2]
[Enter] in each cell.
It doesn't prompt me to Update links when I open the file (I'm guessing
that this is because it's VBA code rather than a native Excel
formula).

Correct, they're not links per se.
Is there a way to get the pull() function to update on fileopen?

If the [Ctrl]+[Alt]+[F9] full recalc suggested above works (and it should), and
if you're running Excel 2000 or later, add an Open event handler in the
ThisWorkbook class module in the VBE, and enter

Application.CalculateFull

to it.
 
You might try:

Application.Volatile as the first line after the declaration in the
UDF

But if it doesn't work remove it.

It will suck performance out of your workbook as these cells will
recalculate after every change.
...

Which is why I purposefully didn't make it volatile. Better to use full recalc
as needed, which could be added to Open, BeforePrint and BeforeSave event
handlers.
 
Hi Harlan!

I agree that application.volatile isn't desirable especially with a
large workbook.

But then manual full recalc isn't desirable either but if that's the
path adopted, I certainly agree that those event handlers are vital.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
...
...
But then manual full recalc isn't desirable either but if that's the
path adopted, I certainly agree that those event handlers are vital.
...

*Manual* full recalc is only necessary in XL97 and prior. In later versions, you
could add Application.CalculateFull to the SheetCalculate event handler. It
does seem kinda silly to do so rather than having Full Calculation as an option
for automatic calculation, but that's not an option currently.
 
Both Ctrl Alt F9 and the Open event handler work fine.

Thanks guys, you've been great help.
 
Back
Top