Is the "sending" workbook really named book2.xls?

Is it open in the same instance of excel?

Is the name of the worksheet inside book2.xls really named sheets1 (with that
extra S)?

All 3 of those have to be true for =indirect() to work ok.

BimboUK wrote:

Thanks to everybody for the very thoughful and comprehensive response.

Now I know I am doing it right it seems that I have a basic problem that may
be part of the background set-up.

Even when I try the most basic test supplied by Jacob Skania it comes back
with a #REF error.

If I can't do this basic thing then no wonder I am having probs doing the
more complex stuff.

When evaluating it gets to INDIRECT("[Book2.xls]Sheets1!A1")

the next step of the evaluation comes up with the #REF error.

Any ideas whats going wrong - I have tried Hello and 'Hello' in A1

I think this is were my real problem is!!!
I greatly appreciate your comments about how best to deal with my original
problem and when i get the basics working will try the others!!!

You thoughts greatly appreciated.

"Lori Miller" wrote:

OTOH, if the file does exist and there are multiple columns, multiple
Data Text to Columns commands will take longer than a single Edit
Replace.

Not necessarily - depends how many cells and columns... see test below.
Text to Columns is substantially faster but you may need to update links
after. The difference can be large when linking to files across a network.

There is another option too which works on multiple columns - use the
office clipboard: Ctrl+C twice, click the paste icon and then paste special
as TEXT. (With this, you don't need to pastespecial values beforehand).

A test, linking to a column in a closed workbook on a local drive, gave the
following results in seconds. The column consisted of 10,000 random nos.

16.1 cells.Replace "=","="
1.1 columns(1).TextToColumns ,xlDelimitedt=timer:?timer-t
0.2 activeworkbook.UpdateLink activeworkbook.LinkSources()(1)

With office clipboard active then pasting as text (from notepad):
2.0 columns(1).copy
1.4 activesheet.paste

[ All commands executed from the immediate window using
t=timer:Command:?timer-t ]




--

Dave Peterson