Linking Groups of cells between workbooks

V

vnacj-joe

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
 
P

paul

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 said:
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
Click to expand...
 
V

vnacj-joe

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

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
--
Janice


vnacj-joe said:
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
Click to expand...
 
J

Janice

--
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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to copy a range of cells and paste into a new workbook in differentcolumns 3
Showing the top result from a range of cells 6
deleting workbooks as i go 4
MAX AVERAGE MIN with additional criteria, ignoring blank cells 6
Linking workbooks together 1
link workbooks 0
SUMIF linking to closed workbooks. 6
Linked Workbooks - adding lines and keeping links 0

Top