Thanks Dave! I'll try that out. And thanks for the link.

Matt

"Dave Peterson" wrote:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But if you put:
'S:\Projections\Fall 2005\[Nov05.xls]Projection'!
in A1
and
A1
in B1

Then you could use:
=pull(a1&b1)
in any cell

One word of warning.

Look at the cell A1.
If you don't see the leading apostrophe, then excel is "eating it up" to force
text.

Put this in A1.
''S:\Projections\Fall 2005\[Nov05.xls]Projection'!

You should be able to see both leading apostrophes in the formula bar, but only
one in cell.

An alternative:
Put
S:\Projections\Fall 2005\[Nov05.xls]Projection
in A1
put A1 in B1 (I find that confusing!)
then use:
=pull("'"&A1&"'!"&B1)
in your formula cell.




Matt S. R. wrote:

Thanks Chip. I managed to do what you said, however I do not know anything
about VB and am assuming I need to know how to do a little programming to get
the use out of it, which I do not. Thanks for the help anyway.

"Chip Pearson" wrote:

In the VBA editor, go to the File menu, choose Import File, and
choose the Pull.bas file.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Matt S. R." wrote in message
...
Dave,

I got the PULL.zip and extracted it tomy desktop. Now Im not
sure what to
do with the PULL.bas file I have now.

Matt

"Dave Peterson" wrote:

The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that
other workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the
value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Matt S. R. wrote:

I want to be able to change the name of a file that is used
to update data
without using edit links change source, for an existing
link. I would
like to be able to just enter in the file name by hand in
one cell and have
another cell use that new filename to complete it's formula.
If I simply
link cell B1 in the file I am working in to cell A1 on a tab
called
"Projection" in a file called "Nov05" on a network drive
"S:", the formula in
the cell might look like:

='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

What I would like to be able to do is in Cell A1 of the
file I am working
in, be able to hand write the file name (or full path if
necessary) and have
the formula in cell B1 not link directly to the source file
but link to cell
A1 in the same file, to know which source file to get the
data from. For
example, I would want cell A1 to maybe have
'S:\Projections\Fall
2005\[Nov05.xls]Projection'! in it while cell b1 would have
the cell location
of the data. Cell B1 would look to cell A1 to get the
path/file/tab to go to
and might just look like:

=A1$A$1

instead of a direct link which would look like:

='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

I've tried several ways to combine the data in different
cells to get a
working formula and tried to use the Help function to no
avail. If this is
even possible to do something like this, I would really
appreciate any help.
Thanks.

Matt



--

Dave Peterson





--

Dave Peterson