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.

[paul]

indirect will do what you want(with a couple of wrinkles)
watch this thread
http://www.microsoft.com/office/comm...9-539fe5e8583b
--
paul
[email protected]
remove nospam for email addy!



"helptildette" wrote:

>
> 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.
>
>
> --
> helptildette
> ------------------------------------------------------------------------
> helptildette's Profile: http://www.excelforum.com/member.php...o&userid=36372
> View this thread: http://www.excelforum.com/showthread...hreadid=561567
>
>