Cell reference to make a directory path

[Guest]

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

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

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

 

[Harlan Grove]

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?

....

Paths are only necessary when referring to closed workbooks. There's no
built-in means of referring into closed workbooks other than by using
external reference links, e.g.,

='C:\Excel\Project\[Test.xls]Sheet1'!$B$2

and such external reference links can't be built by formula directly.

See the following article in the archives for alternatives.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

 

[Guest]

Thanks for the prompt reply.

If multiple people will be accessing this file from different PC's, will
they also need the addin?

Thanks,

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

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

 

[Guest]

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,

 

[Pete_UK]

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

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,

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 -

 

[Guest]

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.

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

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,

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 -

 

[Pete_UK]

I understand what you are trying to do, and both Dave and Harlan have
given you comments earlier related to the formula-driven approach.
With my suggestion, you don't need to put the whole path into the Edit/
Replace window - just the part that you want to change (i.e. the month
and week, as I indicated before).

Another approach would be to record a macro while you do the edit/
replace once and then edit the macro to pick up the find and
replacement values from two cells somewhere - you could allocate a
keyboard shortcut to this (eg CTRL-SHIFT_U), so that subsequently all
you would need to do is enter the find and replacement values in the
chosen cells and then just CTRL-SHIFT-U to update the file. Almost
along the lines of what you want.

Hope this helps.

Pete

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.

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

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,
:
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 -- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

Yep.

Thanks for the prompt reply.

If multiple people will be accessing this file from different PC's, will
they also need the addin?

Thanks,

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

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

 

[Guest]

Pete, thanks for the suggestions. I'll give it a shot and shout back if
something comes up.

Thanks a ton for the help people!

I understand what you are trying to do, and both Dave and Harlan have
given you comments earlier related to the formula-driven approach.
With my suggestion, you don't need to put the whole path into the Edit/
Replace window - just the part that you want to change (i.e. the month
and week, as I indicated before).

Another approach would be to record a macro while you do the edit/
replace once and then edit the macro to pick up the find and
replacement values from two cells somewhere - you could allocate a
keyboard shortcut to this (eg CTRL-SHIFT_U), so that subsequently all
you would need to do is enter the find and replacement values in the
chosen cells and then just CTRL-SHIFT-U to update the file. Almost
along the lines of what you want.

Hope this helps.

Pete

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.

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.

On Jul 25, 5:08 pm, Dan Deschambault
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:

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.

:
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 -- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

Glad to be of help, Dan.

Pete

Pete, thanks for the suggestions. I'll give it a shot and shout back if
something comes up.

Thanks a ton for the help people!

I understand what you are trying to do, and both Dave and Harlan have
given you comments earlier related to the formula-driven approach.
With my suggestion, you don't need to put the whole path into the Edit/
Replace window - just the part that you want to change (i.e. the month
and week, as I indicated before).

Another approach would be to record a macro while you do the edit/
replace once and then edit the macro to pick up the find and
replacement values from two cells somewhere - you could allocate a
keyboard shortcut to this (eg CTRL-SHIFT_U), so that subsequently all
you would need to do is enter the find and replacement values in the
chosen cells and then just CTRL-SHIFT-U to update the file. Almost
along the lines of what you want.

Hope this helps.

Pete

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.
:
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
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,
:
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 -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -