Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
vnacj-joe
 
Posts: n/a
Default Linking Groups of cells between workbooks

While composing this question I found an alternate method of linking groups
of cells between workbooks in the help file. I'm submitting this question
anyway, because I would like to know what caused the inconsistant results
with the method I was using.
If you found this question during a search and are having problems linking
groups of cells between workbooks go to the end of the message for a possible
solution which I found in the Excel 97 help file.

I've been having a problem with both excel 2002 and 97, I only have access
to 97 this evening.
I'm linking a portion of a column from one workbook to another. The number
of cells linked between workbooks is the same, but the column and row
references can differ.

The problem is that I'm using the same set of keystrokes each time I create
a link, sometimes it works, other times the cells display #VALUE! instead of
the data from the linked workbook.
Here are two examples using the actual cell references. The first worked
correctly, the second gave me a #VALUE! error.

1. This instance displays the CORRECT data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$3:$G$22 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$3:$F$22
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see the correct data from the
DataCollector workbook in cells $G$3:$G$22.
All 20 cells ($G$3:$G$22) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$3:$F$22

2. This instance displays #VALUE! instead of the correct data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$42:$G$49 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$49:$F$56
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see #VALUE! in cells $G$42:$G$49.
All 8 cells ($G$42:$G$49) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$49:$F$56

The cell formatting is the same in the linked cells.

Any Thoughts?

----------------------------------------------------------------------------------

Method for linking groups of cells between workbooks that I found in the
Excel help files:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select and copy cells $F$49:$F$56 in DataCollector
I select the DisplayBook
I select cell $G$42
I paste special and click paste link
The correct data fills the 8 cells $G$42:$G$49
The 8 cells ($G$42:$G$49) show the following links:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F49
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F50
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F51
Etc...
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F56

Here is the actual help file I found:
CREATE A LINK TO WORKSHEET DATA ON YOUR INTRANET OR THE INTERNET

1 Open the workbook on your intranet or the Internet that contains the data
you want to link to.
2 Select the data in the workbook, and click Copy on the Edit menu.
3 Open the Microsoft Excel workbook where you want to create the link.
4 Click the upper-left cell of the area where you want to see the linked data.
5 On the Edit menu, click Paste Special.
6 Click Paste Link.

Note To create a link without opening the workbook on the Internet, click
the cell where you want the link, and type an equal sign (=) and the URL
address, followed by the location in the workbook. For example, type
=[http://www.someones.homepage/file.xls]Sheet1!A1
or
=[ftp://ftp.server.somewhere/file.xls]Sheet1!MyNamedCell




  #2   Report Post  
Posted to microsoft.public.excel.misc
paul
 
Posts: n/a
Default Linking Groups of cells between workbooks

i cant tell you exactly why only point out what I see,your first example have
the same row numbers but different column number,whereas your second example
has different column and row numbers.If you paste an array reference shouldnt
you use ctrl shift enter?
--
paul
remove nospam for email addy!



"vnacj-joe" wrote:

While composing this question I found an alternate method of linking groups
of cells between workbooks in the help file. I'm submitting this question
anyway, because I would like to know what caused the inconsistant results
with the method I was using.
If you found this question during a search and are having problems linking
groups of cells between workbooks go to the end of the message for a possible
solution which I found in the Excel 97 help file.

I've been having a problem with both excel 2002 and 97, I only have access
to 97 this evening.
I'm linking a portion of a column from one workbook to another. The number
of cells linked between workbooks is the same, but the column and row
references can differ.

The problem is that I'm using the same set of keystrokes each time I create
a link, sometimes it works, other times the cells display #VALUE! instead of
the data from the linked workbook.
Here are two examples using the actual cell references. The first worked
correctly, the second gave me a #VALUE! error.

1. This instance displays the CORRECT data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$3:$G$22 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$3:$F$22
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see the correct data from the
DataCollector workbook in cells $G$3:$G$22.
All 20 cells ($G$3:$G$22) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$3:$F$22

2. This instance displays #VALUE! instead of the correct data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$42:$G$49 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$49:$F$56
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see #VALUE! in cells $G$42:$G$49.
All 8 cells ($G$42:$G$49) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$49:$F$56

The cell formatting is the same in the linked cells.

Any Thoughts?

----------------------------------------------------------------------------------

Method for linking groups of cells between workbooks that I found in the
Excel help files:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select and copy cells $F$49:$F$56 in DataCollector
I select the DisplayBook
I select cell $G$42
I paste special and click paste link
The correct data fills the 8 cells $G$42:$G$49
The 8 cells ($G$42:$G$49) show the following links:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F49
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F50
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F51
Etc...
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F56

Here is the actual help file I found:
CREATE A LINK TO WORKSHEET DATA ON YOUR INTRANET OR THE INTERNET

1 Open the workbook on your intranet or the Internet that contains the data
you want to link to.
2 Select the data in the workbook, and click Copy on the Edit menu.
3 Open the Microsoft Excel workbook where you want to create the link.
4 Click the upper-left cell of the area where you want to see the linked data.
5 On the Edit menu, click Paste Special.
6 Click Paste Link.

Note To create a link without opening the workbook on the Internet, click
the cell where you want the link, and type an equal sign (=) and the URL
address, followed by the location in the workbook. For example, type
=[http://www.someones.homepage/file.xls]Sheet1!A1
or
=[ftp://ftp.server.somewhere/file.xls]Sheet1!MyNamedCell




  #3   Report Post  
Posted to microsoft.public.excel.misc
vnacj-joe
 
Posts: n/a
Default Linking Groups of cells between workbooks

Paul,

Both observations helped. ctrl "shift" enter seems to work every time.
RE Row numbers:
I tried linking other areas of the worksheet using ctrl enter. If I select
the same row numbers in both workbooks it works, but if I select unmatched
row numbers there is always some sort of problem.

Seems like a simple solution, but I spent a lot of time searching the groups
for help on multi cell links and was extemely frustrated.

Thanks for your response.
Joe


"paul" wrote:

i cant tell you exactly why only point out what I see,your first example have
the same row numbers but different column number,whereas your second example
has different column and row numbers.If you paste an array reference shouldnt
you use ctrl shift enter?
--
paul
remove nospam for email addy!



"vnacj-joe" wrote:

While composing this question I found an alternate method of linking groups
of cells between workbooks in the help file. I'm submitting this question
anyway, because I would like to know what caused the inconsistant results
with the method I was using.
If you found this question during a search and are having problems linking
groups of cells between workbooks go to the end of the message for a possible
solution which I found in the Excel 97 help file.

I've been having a problem with both excel 2002 and 97, I only have access
to 97 this evening.
I'm linking a portion of a column from one workbook to another. The number
of cells linked between workbooks is the same, but the column and row
references can differ.

The problem is that I'm using the same set of keystrokes each time I create
a link, sometimes it works, other times the cells display #VALUE! instead of
the data from the linked workbook.
Here are two examples using the actual cell references. The first worked
correctly, the second gave me a #VALUE! error.

1. This instance displays the CORRECT data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$3:$G$22 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$3:$F$22
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see the correct data from the
DataCollector workbook in cells $G$3:$G$22.
All 20 cells ($G$3:$G$22) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$3:$F$22

2. This instance displays #VALUE! instead of the correct data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$42:$G$49 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$49:$F$56
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see #VALUE! in cells $G$42:$G$49.
All 8 cells ($G$42:$G$49) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$49:$F$56

The cell formatting is the same in the linked cells.

Any Thoughts?

----------------------------------------------------------------------------------

Method for linking groups of cells between workbooks that I found in the
Excel help files:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select and copy cells $F$49:$F$56 in DataCollector
I select the DisplayBook
I select cell $G$42
I paste special and click paste link
The correct data fills the 8 cells $G$42:$G$49
The 8 cells ($G$42:$G$49) show the following links:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F49
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F50
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F51
Etc...
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F56

Here is the actual help file I found:
CREATE A LINK TO WORKSHEET DATA ON YOUR INTRANET OR THE INTERNET

1 Open the workbook on your intranet or the Internet that contains the data
you want to link to.
2 Select the data in the workbook, and click Copy on the Edit menu.
3 Open the Microsoft Excel workbook where you want to create the link.
4 Click the upper-left cell of the area where you want to see the linked data.
5 On the Edit menu, click Paste Special.
6 Click Paste Link.

Note To create a link without opening the workbook on the Internet, click
the cell where you want the link, and type an equal sign (=) and the URL
address, followed by the location in the workbook. For example, type
=[http://www.someones.homepage/file.xls]Sheet1!A1
or
=[ftp://ftp.server.somewhere/file.xls]Sheet1!MyNamedCell




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Linking Groups of cells between workbooks

I need a little help. This is the first time I have used this help line and
I need a little assistance with a simple question.

I would like to do the following: If a value in column A in one workbook is
greater than 299999 then copy the information from four other fields into
another workbook. I need these to automatically update.

Please help me with this.

Janice
--
Janice


"vnacj-joe" wrote:

While composing this question I found an alternate method of linking groups
of cells between workbooks in the help file. I'm submitting this question
anyway, because I would like to know what caused the inconsistant results
with the method I was using.
If you found this question during a search and are having problems linking
groups of cells between workbooks go to the end of the message for a possible
solution which I found in the Excel 97 help file.

I've been having a problem with both excel 2002 and 97, I only have access
to 97 this evening.
I'm linking a portion of a column from one workbook to another. The number
of cells linked between workbooks is the same, but the column and row
references can differ.

The problem is that I'm using the same set of keystrokes each time I create
a link, sometimes it works, other times the cells display #VALUE! instead of
the data from the linked workbook.
Here are two examples using the actual cell references. The first worked
correctly, the second gave me a #VALUE! error.

1. This instance displays the CORRECT data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$3:$G$22 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$3:$F$22
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see the correct data from the
DataCollector workbook in cells $G$3:$G$22.
All 20 cells ($G$3:$G$22) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$3:$F$22

2. This instance displays #VALUE! instead of the correct data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$42:$G$49 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$49:$F$56
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see #VALUE! in cells $G$42:$G$49.
All 8 cells ($G$42:$G$49) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$49:$F$56

The cell formatting is the same in the linked cells.

Any Thoughts?

----------------------------------------------------------------------------------

Method for linking groups of cells between workbooks that I found in the
Excel help files:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select and copy cells $F$49:$F$56 in DataCollector
I select the DisplayBook
I select cell $G$42
I paste special and click paste link
The correct data fills the 8 cells $G$42:$G$49
The 8 cells ($G$42:$G$49) show the following links:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F49
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F50
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F51
Etc...
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F56

Here is the actual help file I found:
CREATE A LINK TO WORKSHEET DATA ON YOUR INTRANET OR THE INTERNET

1 Open the workbook on your intranet or the Internet that contains the data
you want to link to.
2 Select the data in the workbook, and click Copy on the Edit menu.
3 Open the Microsoft Excel workbook where you want to create the link.
4 Click the upper-left cell of the area where you want to see the linked data.
5 On the Edit menu, click Paste Special.
6 Click Paste Link.

Note To create a link without opening the workbook on the Internet, click
the cell where you want the link, and type an equal sign (=) and the URL
address, followed by the location in the workbook. For example, type
=[http://www.someones.homepage/file.xls]Sheet1!A1
or
=[ftp://ftp.server.somewhere/file.xls]Sheet1!MyNamedCell




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Linking Groups of cells between workbooks


--
Janice
I need a little help. This is the first time I have used this help line and
I need a little assistance with a simple question.

I would like to do the following: If a value in column A in one workbook is
greater than 299999 then copy the information from four other fields into
another workbook. I need these to automatically update.

Please help me with this.

Janice

"paul" wrote:

i cant tell you exactly why only point out what I see,your first example have
the same row numbers but different column number,whereas your second example
has different column and row numbers.If you paste an array reference shouldnt
you use ctrl shift enter?
--
paul
remove nospam for email addy!



"vnacj-joe" wrote:

While composing this question I found an alternate method of linking groups
of cells between workbooks in the help file. I'm submitting this question
anyway, because I would like to know what caused the inconsistant results
with the method I was using.
If you found this question during a search and are having problems linking
groups of cells between workbooks go to the end of the message for a possible
solution which I found in the Excel 97 help file.

I've been having a problem with both excel 2002 and 97, I only have access
to 97 this evening.
I'm linking a portion of a column from one workbook to another. The number
of cells linked between workbooks is the same, but the column and row
references can differ.

The problem is that I'm using the same set of keystrokes each time I create
a link, sometimes it works, other times the cells display #VALUE! instead of
the data from the linked workbook.
Here are two examples using the actual cell references. The first worked
correctly, the second gave me a #VALUE! error.

1. This instance displays the CORRECT data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$3:$G$22 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$3:$F$22
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see the correct data from the
DataCollector workbook in cells $G$3:$G$22.
All 20 cells ($G$3:$G$22) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$3:$F$22

2. This instance displays #VALUE! instead of the correct data:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select cells $G$42:$G$49 in DisplayBook
I press the "=" key
I select the DataCollector book
I select cells $F$49:$F$56
I press Ctrl Enter (Ctrl Return)
I'm taken back to DisplayBook and I see #VALUE! in cells $G$42:$G$49.
All 8 cells ($G$42:$G$49) show the following link:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!$F$49:$F$56

The cell formatting is the same in the linked cells.

Any Thoughts?

----------------------------------------------------------------------------------

Method for linking groups of cells between workbooks that I found in the
Excel help files:
The two workbooks are the DisplayBook and the DataCollector, both are open.
I select and copy cells $F$49:$F$56 in DataCollector
I select the DisplayBook
I select cell $G$42
I paste special and click paste link
The correct data fills the 8 cells $G$42:$G$49
The 8 cells ($G$42:$G$49) show the following links:
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F49
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F50
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F51
Etc...
=[QIIG_DataCollector_2006.xls]QIIG_DataCollector_2006!F56

Here is the actual help file I found:
CREATE A LINK TO WORKSHEET DATA ON YOUR INTRANET OR THE INTERNET

1 Open the workbook on your intranet or the Internet that contains the data
you want to link to.
2 Select the data in the workbook, and click Copy on the Edit menu.
3 Open the Microsoft Excel workbook where you want to create the link.
4 Click the upper-left cell of the area where you want to see the linked data.
5 On the Edit menu, click Paste Special.
6 Click Paste Link.

Note To create a link without opening the workbook on the Internet, click
the cell where you want the link, and type an equal sign (=) and the URL
address, followed by the location in the workbook. For example, type
=[http://www.someones.homepage/file.xls]Sheet1!A1
or
=[ftp://ftp.server.somewhere/file.xls]Sheet1!MyNamedCell




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
Linking problems between two workbooks - Excel 2000 SP3 Ramon Setting up and Configuration of Excel 0 January 12th 06 05:01 PM
Linking Merged Cells C. Cunningham Excel Worksheet Functions 2 December 27th 05 10:45 PM
Linking WorkBOOKS across directories maurices5000 Excel Worksheet Functions 1 December 16th 05 10:41 PM
Need assistance linking workbooks by date bsmith Excel Worksheet Functions 2 November 19th 05 09:26 PM
linking to multiple workbooks cwwolfdog Excel Discussion (Misc queries) 4 April 18th 05 05:29 PM


All times are GMT +1. The time now is 02:29 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"