H
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.
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.