TYPO alert!
Dave's text formula is missing a dbl quotes at the end:
="='C:\Documents and Settings\All Users\Documents\Sales order Forms\["
&text(row()+47,"0000")&".xls]Info'!B$2"
Another approach with the exact same concept, where, if you start at "0001",
you can enter on *any* row and copy down:
="='C:\Documents and Settings\All Users\Document\Sales order
Forms\["&TEXT(ROWS($1:1),"0000")&".xls]Info'!B$2"
You can change the starting number by simply adjusting the Rows() function:
Rows($1:1)
Rows($1:48)
or whatever!
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
What you'd want to use is =indirect() and build a formula that results in
the
string that points at the folder, file, sheet, location.
But the bad thing is that =indirect() won't work if that sending file is
closed.
If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm
That includes =indirect.ext() that may help you.
=============
If I had to do this just a single time, I'd use this technique:
I'd build a formula that would create a string that looked like your
formula:
Put this in A1:
="='C:\Documents and Settings\All Users\Documents\Sales order Forms\["
&text(row()+47,"0000")&".xls]Info'!B$2
If you use a different row, then adjust this portion so that it gives the
correct starting value:
text(row()+47,"0000")
So if you put this in row 3 and want to start with 0048.xls, you'd add 45.
Double check your typing now!
Then drag down as far as you need.
You'll end up with a string that looks like your formulas.
Then select that range and convert it to values
(Copy|paste special|values)
Now select that column.
Data|text to columns
delimited (but don't choose anything)
And plop it right back where you got it.
Excel will see this as you re-editing each formula.
And you should have your results after excel recalcs.
I have a production worksheet that links to multiple invoices saved as
individual files. I'm trying to get the information to pull automatically
from these invoices, and it's not moving on to the next invoice in
sequence.
This is what the formula looks like:
='C:\Documents and Settings\All Users\Documents\Sales order
Forms\[0048.xls]Info'!B$2
I'm getting hung up on the [0048.xls} and getting it to recognize there is
a
0049, 0050, etc. to pull from. Can anyone help?
