PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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.

If they're in separate instances of excel, I don't think it's possible to have
automatic updates.

Have you tried one instance of excel and arranging your windows so that one
window is on one monitor and the other is on the other monitor?

(I _think_ I've seen some posts that say that this is possible--but I could be
wrong.)

KandK wrote:
>
> 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.

--

Dave Peterson

No I haven't tried that yet, the second monitor is only going to be bought on
the strength that I can have something viable running. Just out of curiosity
the second program does not have to be Excel it can be any program that will
just display relevant information from the first, it should never have any
information amended on it once it is set up. Are you aware of any program
that I can use that will give the desired results. Once again many thanks for
your help.

I don't have any firsthand knowledge of using multiple monitors.

Maybe you could start a new thread with a better subject or search google
looking through the *excel* newsgroups.

KandK wrote:
>
> No I haven't tried that yet, the second monitor is only going to be bought on
> the strength that I can have something viable running. Just out of curiosity
> the second program does not have to be Excel it can be any program that will
> just display relevant information from the first, it should never have any
> information amended on it once it is set up. Are you aware of any program
> that I can use that will give the desired results. Once again many thanks for
> your help.

--

Dave Peterson