If you have your formulae already set up, looking at c:\Excel\Project
\July\Week_1\Jim.xls for example, then all you need to do is highlight
all the cells with the formulae in and do Edit | Replace (or CTRL-H)
and:

Find what: July\Week_1\
Replace with: July\Week_2\

Click Replace All and now your formulae will be looking at the Week2
file. Quite a simple operation to do each week.

Hope this helps.

Pete

On Jul 25, 5:08 pm, Dan Deschambault
wrote:
Hmm, ok maybe I should elaborate a little more.

I'm currently referring to external files using the SUM function which
retrieves the data properly. The problem is, I have approximately 40 rows of
data with 12 columns to update every week and month.

Lets say I have files such as:

c:\Excel\Project\July\Week_1\Jim.xls
c:\Excel\Project\July\Week_2\Jim.xls
c:\Excel\Project\July\Week_3\Jim.xls
c:\Excel\Project\July\Week_4\Jim.xls
c:\Excel\Project\August\Week_1\Jim.xls

What I want to do is make a generic reference to (D1) c:\Excel\Project\ and
then be able to narrow it down by entering the (D2) month, (D3) week and (D4)
file name in seperate cells. Then refer to D1, D2, D3, D4 to complete the
path, rather than editing 480 cells.

Essentially what I'm trying to accompish is creating a path by referring to
cells within the worksheet. Writing it out manually is working fine, but just
takes way too long.

Thanks,



"Dan Deschambault" wrote:
I have a spreadsheet which pulls 3 sets of data from approximately 40
different files.

I need to update the referred path of each cell every week.

Is there a way to make "partial" path names in seperate cells and
essentially create the total path by referring to those cells?

Example:

A1: "'c:\"
A2: "Excel\"
A3: "Project\"
A4: "[Test.xls]Sheet1'!$B$2"

Then refer to A1, A2, A3, A4 to create a cell reference to
"'c:\Excel\Project\[Test.xls]Sheet1'!$B$2"

This way, I could simply change the data in a few cells and and update my
whole spreadsheet.

-Working on Excel 2003-

Thanks for any input!

Dan- Hide quoted text -

- Show quoted text -