CONCATENATE within links to build different filenames based on for
 

I have a series of files, all the same layout, where the only difference is
the filename, which is based on the date the file refers to i.e. 'Figures 01
Mar.xls'.

Is there away I can build a cell link along the lines of
='C:\DOCS|FIGURES\Figures '&DAY(M26)&' Mar.xls]Sheet 1!$M27?

Cheers

You need to use VBA for that, Harlan Grove has written a function called
PULL

http://groups.google.co.uk/groups?se...wsranger.co m

if the other workbook is open you can use INDIRECT

--
Regards,

Peo Sjoblom


"Captain Scarlett" wrote in
message ...
I have a series of files, all the same layout, where the only difference is
the filename, which is based on the date the file refers to i.e. 'Figures
01
Mar.xls'.

Is there away I can build a cell link along the lines of
='C:\DOCS|FIGURES\Figures '&DAY(M26)&' Mar.xls]Sheet 1!$M27?

Cheers

You'd want to use =indirect() in your formula, but =indirect() won't work with
closed workbooks.

But Harlan Grove wrote a User Defined Function that can pull from a closed
workbook:

http://groups.google.co.uk/groups?se...wsranger.co m

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

Captain Scarlett wrote:

I have a series of files, all the same layout, where the only difference is
the filename, which is based on the date the file refers to i.e. 'Figures 01
Mar.xls'.

Is there away I can build a cell link along the lines of
='C:\DOCS|FIGURES\Figures '&DAY(M26)&' Mar.xls]Sheet 1!$M27?

Cheers

--

Dave Peterson

Dave, Peo

Thanks, will give it a try.

KR
Capt S

"Dave Peterson" wrote:

You'd want to use =indirect() in your formula, but =indirect() won't work with
closed workbooks.

But Harlan Grove wrote a User Defined Function that can pull from a closed
workbook:

http://groups.google.co.uk/groups?se...wsranger.co m

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

Captain Scarlett wrote:

I have a series of files, all the same layout, where the only difference is
the filename, which is based on the date the file refers to i.e. 'Figures 01
Mar.xls'.

Is there away I can build a cell link along the lines of
='C:\DOCS|FIGURES\Figures '&DAY(M26)&' Mar.xls]Sheet 1!$M27?

Cheers

--

Dave Peterson