Filename in a formula generated automatically?

[helptildette]

Hi,

I get a new workbook with data mailed to me every week, the filename is
"Filename week ##.xls" (replace # with actual week number)

I have one workbook that looks up values from those weekly workbooks
and displays it all nicely, it looks something like this:

Columns:
A: Weeknumber
B: ='D:\Year\[Filename week ##.xls]Sheet4'!$B$30
C: ='D:\Year\[Filename week ##.xls]Sheet2'!$F$8
D: ='D:\Year\[Filename week ##.xls]Sheet2'!$F$10
E: =B+C+D

There are many more columns, the sheet has hundreds of lines and there
are several sheets that do similar things.

Today I copy the whole line from last week and edit every cell so they
look up the correct weekly workbook. This may introduce the feared
human error, and ofcourse I want this to be done automatically so I
don't have to.

What I'm looking for is something like this:
='D:\Year\[Filename week '*+A##+*'.xls]Sheet4'!$B$30
so that the weeknumber from column A is added easily to the filename in
all the formulas on that line.

Do anyone have any suggestions for me?

If I didn't explain well enough let me know and I'll try again I'm
using Excel 2000.

 

[Guest]

indirect will do what you want(with a couple of wrinkles)
watch this thread
http://www.microsoft.com/office/com...r=US&mid=6fdbaf91-39a2-448e-8e79-539fe5e8583b