Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking problems between two workbooks - Excel 2000 SP3 | Setting up and Configuration of Excel | |||
Linking Merged Cells | Excel Worksheet Functions | |||
Linking WorkBOOKS across directories | Excel Worksheet Functions | |||
Need assistance linking workbooks by date | Excel Worksheet Functions | |||
linking to multiple workbooks | Excel Discussion (Misc queries) |