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.

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

Just to add on a little to Dave's good idea ..
.. Maybe =index() would would work for you.

Instead of:
=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))

Try something like:
=IF(ISNA(MATCH($D$2-1,'[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$IJ$2,0)),"",INDEX('[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$IJ$140,,MATCH($D$2-1,'[BOOKINGS.xls]NOV 2005 - JUNE
2006'!$C$2:$IJ$2,0)))

Above is untested but think it should work ok ..

The changes:
1. Removed the extraneous "-1" bit from within the: ISNA(MATCH(...)-1)
(overlooked this earlier, sorry)

2. Amended the OFFSET to suit INDEX, viz.:
a. Indexed range is $C$2:$IJ$140 (changed from the OFFSET's $C$2:$C$140)
b. Adjusted OFFSET's "MATCH(...)-1" part, i.e. removed the arithmetic "-1"
adjustment from "MATCH(...)-1" which is not required in
INDEX(...,,MATCH(...))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

This works great, it is now seeing the information on opening the program and
also changes the information when the day changes (there is three columns on
the worksheet, yesterday, today and tomorrow). However for some reason when I
enter information on the first workbook (Bookings) it does not automatically
update the information onto the other program. I have the Update remote
references in Tools checked and also the Automatic option in the Links dialog
box is checked. Am I doing something wrong or won't this sort of link
automatically update. Any help you can give will be greatly appreciated. Many
thanks.

If you're still opening the files in different instances, then the "sending"
file needs to be saved before any change will be reflected in the first
workbook.

Then in the receiving workbook, you can use Edit|Links|Update values to get the
newly saved values.

Or if there's only a couple of cells, you can:
select the cell
hit F2, then enter

Excel will think that you're reentering a new formula and reevaluate it.

KandK wrote:

This works great, it is now seeing the information on opening the program and
also changes the information when the day changes (there is three columns on
the worksheet, yesterday, today and tomorrow). However for some reason when I
enter information on the first workbook (Bookings) it does not automatically
update the information onto the other program. I have the Update remote
references in Tools checked and also the Automatic option in the Links dialog
box is checked. Am I doing something wrong or won't this sort of link
automatically update. Any help you can give will be greatly appreciated. Many
thanks.

--

Dave Peterson

When I had originally set this up both workbooks opened in the same window
and it worked great, instantly updating the information as it was entered.
Unfortunately as I I do need them open in seperate windows and I need for
them to automatically update, is this possble at all? Thanks for all the help
I really appreciate it.