Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.links
|
|||
|
|||
Stubborn Link Data/Value Problem - Help Appreciated!
Hello,
I am having a problem with a link in one of my Excel worksheets and I'm hoping that someone can help me with it. Here's the situation... I have two separate workbooks that are connected via a link such that an update in one workbook is automatically (and immediately) reflected in the second book. There is a cell (say A1) in the second book (the destination) that contains a link with the following format: =Excel.Sheet.8|'C:\Folder\Source.xls'!'!Sheet1!R2C 1' [I created this link by copying the source cell from the source workbook and pasting (using Paste_Special) in the destination cell in the destination workbook.] NOW...this part works great! No problems at this stage. An update in the Source workbook is reflected in the Destination workbook just as I want it. BUT...here's the problem. I have a cell, A5, in the destination workbook that is supposed to perform a calculation based on the value in cell A1 (the cell containing the link). Unfortunately, even though there is a numeric value displayed in A1, cell A5 shows the value of A1 as zero(0). So, A1: displays a value of 205 (the value obtained from the source workbook via the link... we're good here!) But, despite the fact that I have set... A5: =A1 I am seeing the following A5: displays a value of zero(0) Does anyone know why A5 would display zero when it should be 205? I need A5 to reflect the numeric value of A1. I know that one possible solution is to copy A1 and do Paste_Special-Values to cell A5. This would be fine if the link were going to be updated once in a while. However, the link (A1) will be updated several hundred times per day! Because of this, manually performing a Copy then Paste_Special-Values operation every time the value in A1 changes is unrealistic. Is there a simple way to get A5 to automatically (i.e., no intervention from me) reflect the new value in A1? If this will require VBA, I would appreciate any code that you may be able to provide as I am far from a VBA guru. Thanks. This would be a HUGE help!! |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.links
|
|||
|
|||
Stubborn Link Data/Value Problem - Help Appreciated!
Jax wrote:
I am seeing the following A5: displays a value of zero(0) In the status bar are you seeing "Circular" or "Calculate"? If there is a circular reference in your worksheet then some formulas may not be updated when the sheet is recalculated. If you are not in automatic calculation mode (Tools / Options / Calculation / Automatic) then the formula will not automatically update when the data in A1 changes. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.links
|
|||
|
|||
Stubborn Link Data/Value Problem - Help Appreciated!
I don't think your formula is actually in a cell. I believe it is sitting in
an OLE Object/link above the cell. Select it and delete in. With both workbooks open in the same instance of Excel, In A1 put in a formula =[Source.xls]Sheet1!A2 -- Regards, Tom Ogilvy Jax wrote in message om... Hello, I am having a problem with a link in one of my Excel worksheets and I'm hoping that someone can help me with it. Here's the situation... I have two separate workbooks that are connected via a link such that an update in one workbook is automatically (and immediately) reflected in the second book. There is a cell (say A1) in the second book (the destination) that contains a link with the following format: =Excel.Sheet.8|'C:\Folder\Source.xls'!'!Sheet1!R2C 1' [I created this link by copying the source cell from the source workbook and pasting (using Paste_Special) in the destination cell in the destination workbook.] NOW...this part works great! No problems at this stage. An update in the Source workbook is reflected in the Destination workbook just as I want it. BUT...here's the problem. I have a cell, A5, in the destination workbook that is supposed to perform a calculation based on the value in cell A1 (the cell containing the link). Unfortunately, even though there is a numeric value displayed in A1, cell A5 shows the value of A1 as zero(0). So, A1: displays a value of 205 (the value obtained from the source workbook via the link... we're good here!) But, despite the fact that I have set... A5: =A1 I am seeing the following A5: displays a value of zero(0) Does anyone know why A5 would display zero when it should be 205? I need A5 to reflect the numeric value of A1. I know that one possible solution is to copy A1 and do Paste_Special-Values to cell A5. This would be fine if the link were going to be updated once in a while. However, the link (A1) will be updated several hundred times per day! Because of this, manually performing a Copy then Paste_Special-Values operation every time the value in A1 changes is unrealistic. Is there a simple way to get A5 to automatically (i.e., no intervention from me) reflect the new value in A1? If this will require VBA, I would appreciate any code that you may be able to provide as I am far from a VBA guru. Thanks. This would be a HUGE help!! |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.links
|
|||
|
|||
Stubborn Link Data/Value Problem - Help Appreciated!
Tom Ogilvy wrote:
I don't think your formula is actually in a cell. I believe it is sitting in an OLE Object/link above the cell. Select it and delete in. Well spotted, Tom. I didn't think of that explanation for the curious link formula. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.links
|
|||
|
|||
Stubborn Link Data/Value Problem - Help Appreciated!
Well, first, thanks to all that responded!
Second, although the suggestions that were given were great, I still have one "basic" problem (I stupidly failed to mention this next part before). Here's the question: What if I want to link several workbooks together, with each workbook sitting on a different computer? For example, say workbook1, sitting on computer A, is linked to workbook2, on computer B, via shared network drive P. Workbook2 is linked to workbook3, on computer C, via the same shared drive P. The computers can "see" each other and book2 (on the shared drive) is simply acting as a middleman shuffling data between book1 (on computer A) and book3 (on computer C). I know it's a weird scenrio but I have to work with it (unless someone has any suggestions)... This was the original reason that I used the copy and paste method for linking the workbooks. Unfortunately, that method won't allow me to perform any calculations on the linked data (as we already know). Any suggestions on how to get this scenario to work? And thanks again for your help!!! -J wrote in message ... I think the problem is the way you are linking to A1 in workbook 1. Perhaps I am wrong. If so, my apologies for what I suggest below, :) You say you have "Copied" and "Pasted" the value of Sheet 1, R2C1 in Book 1 into A1 in book 2. Then you set A5 in book 2 to use A1 in book 2 to perform a calculation, eg (Value in A1 x 2). If book 1 Sheet1 R2C1 held 10, and the formula in book 2 A5 held A1 x 2, A5 should show 20. I don't see that Copy and Paste will work. It will only copy the current value of book 1 sheet 1 R2C1 into book 2 cell A1. So, if at some stage the cell in book 1 Sheet 1 R2C1 gets changed, A1 in book 2 will not be updated. What I mean is, when you copied and pasted, if the current value in book 1 Sheet 1 R2C1 was 0, then A1 in book 2 will always be 0. You need to create a dynamic link. Do this; 1] Open both workbooks in Excel. Don't maximise either, have them both so that one overlaps the other but you can click on either to make it active at any stage. 2] In book 2, make sure cell A1 is empty, then click inside it. Press = (the equal key on your keyboard). 3] Make book 1 active by clicking the header bar. 4] In Sheet 1, click cell R2C1. Then click enter. That will finish the action for cell A1 in book 2. 5] In book to empty cell A5 and click = again. 6] Click A1, then enter in the formula bar any other calculation you have to enter, eg =A1*65.345 7] Press enter and cell A5 should then "always" show the calculation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stubborn cell formatting. | Excel Worksheet Functions | |||
Data Link and VLookup Problem | Excel Worksheet Functions | |||
A Very stubborn apostrophe that only shows in editing mode and the formula bar... | Excel Discussion (Misc queries) | |||
.. any help appreciated .. i think it's a pasting HTML problem. | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |