automatic linking to many files

[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

 

[geoff_ness]

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

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

 

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

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

 

[Ron de Bruin]

Hi chris

See also
http://www.rondebruin.nl/summary2.htm

 

[chris]

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/

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

 

[Lori]

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.