Wow! Ok, this helps tremendously and will reduce the time taken to make
changes to the spreadsheet. It's a bit of an alternative to what I'd like to
accomplish though.

What I really want to do is "build a path by referring to a combination of
cells". Your suggestion does help a lot, but the path I'm using is quite long
and it doesn't fit in the Edit | Replace window. I can quickly check the
referenced cell and deduct which to replace which works, albeit a little
slower than almost automating it.

I have 4 weekly columns. Each have 3 subcolumns which refer to different
areas of the weekly file, all on one spreadhseet.

So for each person (examaple: Jim.xls) there's a file for each week (week_1,
week_2, week_3, week_4). If this person is included on the spreadsheet for
the following month, the only change to the path I have to make is the month
name since I'll use a previous copy of this spreadsheet for the next month.

I really just want to be able to change the month and week in the path by
"inserting" the correct data from a seperate cell.

Something along the lines of:

=SUM('c:\Excel\Project\'(A1)'\'(A2)'\[Jim.xls]')

A1 being a cell where I manually enter the month
A2 being a cell where I manually enter the week

Is there any way to insert data from a cell into a path?

Sorry, having a horrible time trying to explain myself.

"Pete_UK" wrote:

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 -