The data|text to columns did return #Ref errors without the prompt.

Thanks for the tip.


Lori Miller wrote:

Actually you're right there isn't much difference in this case
as you're only linking to one cell in each workbook - if you're
linking to a range say H1:H10 its many times faster and you'll
only get the prompt once for each workbook. Also I had tested the
clipboard method for references that are typos eg by omitting an
apostrophe which it ignores but do trip the Replace method up.

However i think a better alternative is to use Text to Columns
after pasting values to convert to formulas (using either tab
delimited or fixed width and skip the $$$s). Then you can select
all links in the EditLinks dialog and update, this allows you to
cancel after the first prompt if you wish and you won't get caught up in
those repeated dialogs.

"Dave Peterson" wrote in message
...
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:



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

--

Dave Peterson