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?

You can use the Indirect function to create the address. Try something like:
=indirect("='[C:\Documents and Settings\All Users\Documents\Sales order
Forms\"&Text(a1,"0000")&".xls]Info'!B$2")

where a1 has your invoice number.

Regards,
Fred

Forms]Sheet1'!$B$3
"champagne" wrote in message
...
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?

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.

champagne wrote:

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?

--

Dave Peterson