Filename in a formula generated automatically?
 

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

Filename in a formula generated automatically?
 

indirect will do what you want(with a couple of wrinkles)
watch this thread
http://www.microsoft.com/office/comm...9-539fe5e8583b
--
paul

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