It sounds like you're actually opening the two different files in separate
instances of excel.

If that's true, then each instance of excel doesn't know about the other
instance. So the first instance of excel just knows that bookings.xls is a
closed file.

And there are some worksheet functions that don't work with closed files:
=offset(), =countif(), =sumif()
are a couple.

If you could open both workbooks in the same instance and have multiple windows
(Window|new window) open in that instance, you would see the results.

(I don't know if that will work with multiple monitors, though.)

Alternatively, you may be able to use an alternative worksheet function. Maybe
=index() would would work for you.

KandK wrote:

I have two workbooks that are linked and if I open them in the same window
they work together perfectly. However I need to open them in separate windows
so one can be viewed on a separate monitor. When I do this it asks me if I
want to update the links which I do but the relevant cells just show #VALUE!.
Even if I go to Edit/Links and try to Update Values or Change Source even
though it finds the right work book (it is password protected and asks for
the password) the relevant cells still show #VALUE!. Is what I am trying to
do impossible or am I missing something obvious.
If it helps this is the formula when I open them in the same window:
=IF(ISNA(MATCH($D$2-1,'[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$IJ$2,0)-1),"",OFFSET('[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$C$140,,MATCH($D$2-1,'[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$IJ$2,0)-1))
And this is the formula when I open them in different windows;
=IF(ISNA(MATCH($D$2-1,'C:\Documents and Settings\D
Clarke\Desktop\[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$IJ$2,0)-1),"",OFFSET('C:\Documents and Settings\D
Clarke\Desktop\[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$C$140,,MATCH($D$2-1,'C:\Documents and Settings\D
Clarke\Desktop\[BOOKINGS.xls]NOV 2005 - JUNE 2006'!$C$2:$IJ$2,0)-1))
If anyone could help it would be greatly appreciated. Many thanks.

--

Dave Peterson