Changing worksheet references
After copying worksheets from one workbook into a new one the cell references
in the new workbook refer to values in the old workbook A typical cell reference in the new workbook might look like ='C:\Some Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in the new workbook should be =SomeWorksheet!$AQ$4. There must be several hundred such references in the new workbook. Is there some way, other than manually, to correct the references to the current workbook? TIA, Ken |
Changing worksheet references
Make sure the new workbook is saved. Then, you shoul be able to go to Edit -
Links, and change source to the current workbook, as opposed to an external workbook. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ken Warthen" wrote: After copying worksheets from one workbook into a new one the cell references in the new workbook refer to values in the old workbook A typical cell reference in the new workbook might look like ='C:\Some Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in the new workbook should be =SomeWorksheet!$AQ$4. There must be several hundred such references in the new workbook. Is there some way, other than manually, to correct the references to the current workbook? TIA, Ken |
Changing worksheet references
Maybe you can select this part:
'C:\SomeDirectory\[SomeWorkbook.xlsm] Ctrl+C. Then, Hit Ctrl+F Replace paste the part that you just copied into 'Find what' and finally hit 'Replace all'. Do this on a backup, just in case the result is not what you want. Good luck, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Luke M" wrote: Make sure the new workbook is saved. Then, you shoul be able to go to Edit - Links, and change source to the current workbook, as opposed to an external workbook. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ken Warthen" wrote: After copying worksheets from one workbook into a new one the cell references in the new workbook refer to values in the old workbook A typical cell reference in the new workbook might look like ='C:\Some Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in the new workbook should be =SomeWorksheet!$AQ$4. There must be several hundred such references in the new workbook. Is there some way, other than manually, to correct the references to the current workbook? TIA, Ken |
Changing worksheet references
The other alternative is that when you start copying worksheets over into a
new book, copy them all in one movement, not one at a time. (You can select multiple sheets using either Shift and/or Ctrl). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Make sure the new workbook is saved. Then, you shoul be able to go to Edit - Links, and change source to the current workbook, as opposed to an external workbook. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ken Warthen" wrote: After copying worksheets from one workbook into a new one the cell references in the new workbook refer to values in the old workbook A typical cell reference in the new workbook might look like ='C:\Some Directory\[SomeWorkbook.xlsm]SomeWorksheet'!$AQ$4'. The correct reference in the new workbook should be =SomeWorksheet!$AQ$4. There must be several hundred such references in the new workbook. Is there some way, other than manually, to correct the references to the current workbook? TIA, Ken |
All times are GMT +1. The time now is 11:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com