Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
KandK
 
Posts: n/a
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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
---
  #4   Report Post  
Posted to microsoft.public.excel.misc
KandK
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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.
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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


  #6   Report Post  
Posted to microsoft.public.excel.misc
KandK
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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.


  #7   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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
  #8   Report Post  
Posted to microsoft.public.excel.misc
KandK
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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.
  #9   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel files in separate program windows Jason Dove Excel Discussion (Misc queries) 27 October 24th 08 07:58 PM
Problem with Windows in Task Bar command Jim Excel Discussion (Misc queries) 1 March 10th 06 07:13 PM
Problem with WIndows in Taskbar command Jim Setting up and Configuration of Excel 0 February 25th 06 12:37 AM
Why does shared workbooks uncheck users Windows in Taskbar option jasonstratton Excel Discussion (Misc queries) 2 October 10th 05 03:11 PM
Problem exporting to excel with Windows XP bhigdon Excel Discussion (Misc queries) 1 August 13th 05 12:06 PM


All times are GMT +1. The time now is 02:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"