Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking worksheets
I am trying to copy a worksheet in its entirity into a new workbook. I would
like the two worksheets to be linked so that when the source worksheet is updated, this feeds through to the destination document. I know this is possible through right clicking over the worksheet tab and copying into a new work book. It is possible on my computer at home and at my previous empolyers and friends I have discussed it with have also confirmed this. I have moved into a new job where the version of Excel is 2003 however the method I have explained above does not result in the same outcome. The values are returned but the links are not present unless one types in '='[table a.xls]Sheet1'!$A$4'. If the cell is empty the value '0' is returned. I know through the options tool bar this is rectifiable, however it is a much less straightforward route, particularly when I know it is possible through copying a worksheet. This obviously saves copying forumlas, formats and switching off zero values. No-one in the organisation seems to be able to help, including IT! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking worksheets
Let me repeat this back, to make sure I understand: you have a
workbook with (let's just call it) Sheet A and Sheet B- let's call this the Original. You want to copy Sheet A from Original into another file, let's call it New. Sheet A refers to Sheet B, and should refer only to Sheet B within the local file, not in an external file. Is that correct, or pretty close? I use XL 03 as well, and I have never seen a tab moved in its entirety that does not refer back to the originating file with all that [table a.xls]!Sheet1! business. As a workaround to solve your immediate problem, tho, you can try this: just prior to copying the tab into the New file, highlight all cells (with the button in the upper left corner) and do a search and replace. Search for the = sign and replace with a string of letters such as xyz. This converts dynamic formulas into static text strings. Copy the tab into the New file, then highlight the tab within the New file and do another search/replace, this time searching for xyz and replace with the = sign. Will that get you where you need to go? Dave O |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking worksheets
Hi,
It is recommended that you right-click on the sheet tab and move or copy the worksheet to a new workbook, so that all the cell width, formatting and formulas retain their properties. Note: If you copy the Worskeet content, to a new worksheet, the default property of the new workshet will be applied to the new content. What will happen is that the text wrap property of the text control would be applied to all the cells within the worksheet. Important; It would be a waste of time redoing the whole thing again, when you can reuse your worksheet by moving or coping. You might have to just re-establish the refrences and link if the situation demands. Challa Prabhu "kagh78" wrote: I am trying to copy a worksheet in its entirity into a new workbook. I would like the two worksheets to be linked so that when the source worksheet is updated, this feeds through to the destination document. I know this is possible through right clicking over the worksheet tab and copying into a new work book. It is possible on my computer at home and at my previous empolyers and friends I have discussed it with have also confirmed this. I have moved into a new job where the version of Excel is 2003 however the method I have explained above does not result in the same outcome. The values are returned but the links are not present unless one types in '='[table a.xls]Sheet1'!$A$4'. If the cell is empty the value '0' is returned. I know through the options tool bar this is rectifiable, however it is a much less straightforward route, particularly when I know it is possible through copying a worksheet. This obviously saves copying forumlas, formats and switching off zero values. No-one in the organisation seems to be able to help, including IT! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking worksheets
Hi Dave
Thanks for the feedback. You almost had it but not quite: Original workbook has one sheet (sheet a). I copy sheet a from original into another file called new. New should refer to sheet a automatically and be an exact duplicate. Depending on who I wanted to view the new document I would hide rows / cells and save into a public location. I might make a number of copies of the original sheet which have different views for different people. I would only ever need to update the original workbook sheet a with any changes and these would be reflected in all the 'new' copies of this sheet. But when opened the 'new' copies would ask if you wanted the file to be updated. This doesn't happen where I work and I know it is something that usually does. "Dave O" wrote: Let me repeat this back, to make sure I understand: you have a workbook with (let's just call it) Sheet A and Sheet B- let's call this the Original. You want to copy Sheet A from Original into another file, let's call it New. Sheet A refers to Sheet B, and should refer only to Sheet B within the local file, not in an external file. Is that correct, or pretty close? I use XL 03 as well, and I have never seen a tab moved in its entirety that does not refer back to the originating file with all that [table a.xls]!Sheet1! business. As a workaround to solve your immediate problem, tho, you can try this: just prior to copying the tab into the New file, highlight all cells (with the button in the upper left corner) and do a search and replace. Search for the = sign and replace with a string of letters such as xyz. This converts dynamic formulas into static text strings. Copy the tab into the New file, then highlight the tab within the New file and do another search/replace, this time searching for xyz and replace with the = sign. Will that get you where you need to go? Dave O |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking worksheets | Excel Discussion (Misc queries) | |||
linking worksheets | Excel Worksheet Functions | |||
Linking worksheets | Excel Worksheet Functions | |||
linking across worksheets | Excel Worksheet Functions | |||
linking worksheets | Excel Worksheet Functions |