automatic linking to many files

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

Hi'
the group's been extremely helpful so far so here goes another
question.

I'm using a single file (2007) to gather data from a number of other
ones - all the source files are named according to the same pattern
(filename-three-digit number.xls) Is there a way to make excel change
the number _in the filename_ as I copy down the way it would change
the row number

Here's how it should look like:

='P:\folder1\folder2\[filename-001.xls]Sheet1'!$A$2
='P:\folder1\folder2\[filename-002.xls]Sheet1'!$A$2
='P:\folder1\folder2\[filename-003.xls]Sheet1'!$A$2

I could just reference the file number to a column containing numbers
- but still don't know how to include a reference in the filename.

chris
 
Hi'
the group's been extremely helpful so far so here goes another
question.

I'm using a single file (2007) to gather data from a number of other
ones - all the source files are named according to the same pattern
(filename-three-digit number.xls) Is there a way to make excel change
the number _in the filename_ as I copy down the way it would change
the row number

Here's how it should look like:

='P:\folder1\folder2\[filename-001.xls]Sheet1'!$A$2
='P:\folder1\folder2\[filename-002.xls]Sheet1'!$A$2
='P:\folder1\folder2\[filename-003.xls]Sheet1'!$A$2

I could just reference the file number to a column containing numbers
- but still don't know how to include a reference in the filename.

chris
Click to expand...

INDIRECT would work in this case - try e.g. (NB be careful with the
combination of single and double quotes):

=INDIRECT("'P:\folder1\folder2\[filename-00"&ROW()&".xls]Sheet1'!$A$2"

Copied down from row 1 to row 3 this should give you the result you're
looking for.

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

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

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

=============
You may want to consider building formulas that create strings that look like
those formulas.

Like:
="$$$$$='P:\folder1\folder2\[filename-" & text(row(),"000")
& ".xls]Sheet1'!$A$2"

(you may have to adjust the row -- depending on the cell that gets the formula.

Then you convert these formulas to values.

And finally, you can select the range
edit|replace
what: $$$$$=
with: =
replace all

Excel will now look at the cells and see that they contain formulas--and excel
will reevaluate them.

I'd do it on a small range first. If you make a mistake and the file doesn't
exist, you'll be dismissing lots and lots of dialogs asking what file you really
meant.
Hi'
the group's been extremely helpful so far so here goes another
question.

I'm using a single file (2007) to gather data from a number of other
ones - all the source files are named according to the same pattern
(filename-three-digit number.xls) Is there a way to make excel change
the number _in the filename_ as I copy down the way it would change
the row number

Here's how it should look like:

='P:\folder1\folder2\[filename-001.xls]Sheet1'!$A$2
='P:\folder1\folder2\[filename-002.xls]Sheet1'!$A$2
='P:\folder1\folder2\[filename-003.xls]Sheet1'!$A$2

I could just reference the file number to a column containing numbers
- but still don't know how to include a reference in the filename.

chris
Click to expand...
 
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.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/
Click to expand...

thanks again everybody, I've had a look at morefunc some time ago but
never got it to install on my office pc (running vista business and
office 2007) - in short the installer says "The name: is not valid.
Please reenter it" (it puts a blank line after "the name:") in the
"select program folder" step (whether I want it to create icons or
not). Will give it one more try at home today.

On the other hand thanks for =indirect - I've been using it before
but the idea of -00"&ROW()&".xls was new to me.

chris
 
Try Data>Consolidate, Function: "Count", Check: create links and Reference:

'P:\folder1\folder2\[filename-*.xls]Sheet1'!$A$2

Unhide the rows when it's run.
 
Back
Top