I'm not sure if this will help speed anything up.

I tried it using that formula. I didn't have that folder or any file by that
name.

I was still prompted for each entry that didn't have a correct reference (all 10
in my sample).

And after the (correct) formula is pasted, excel still has to re-evaluate it.

I didn't do any testing. It's just a gut feeling and that's been wrong lots of
times. Did you try it both ways to see if there was a difference in speed?

And a note to the OP.

If you ever decide to move those "sending" files to a network drive, you may
want to think twice. I'd bet money that your local C: drive is way faster than
any network drive.


Lori Miller wrote:

I wouldn't do an edit-replace to convert formulas with that many links -
it's slow and as you say can stall the application with typos or missing
links.

Instead you can evaluate the formulas:

="='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5"

by copying using the office clipboard (edit menu) and pasting using
the icon that appears, then choose Paste Special Text after if necessary.

This will run much faster and only evaluate links that are valid.
You can use the EditLinks dialog to update links after.

Put this formula in C4:
="$$$='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5"
Change the ## to whatever to get the right file name. If the formula is
in row
325, then subtract 324 to get 1.

Drag down as far as you need.

Then convert these formulas to values.

Then do an edit|Replace
what: $$$
with: (leave blank)
replace all

If you're not sure you created the correct formula, you'll want to test it
on
just a few cells. If you've made a mistake, you'll be prompted to select
the
name of the workbook that contains the correct worksheet.

And if you've done this 1000 times, that means 1000 dismissed dialogs or
killing
excel.



MAANI wrote:

I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need
to
use the workbook name in a formula,like ='C:\My Folder\[wo
1.xls]Sheet1'!$H$5
is there a way that I could drag this formula down so it change the
workbook
name to wo 2, then wo 3, and so on ?
Note: I have workbooks name wo 1 up to wo 1000 and its going up

--

Dave Peterson

--

Dave Peterson