| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing multiple worksheet references in formulae | Excel Discussion (Misc queries) | |||
| Chart data references not changing to new tab when worksheet is co | Charts and Charting in Excel | |||
| Changing Cell References To a Different Worksheet in the Same Cell | Excel Worksheet Functions | |||
| changing cell references | Excel Discussion (Misc queries) | |||
| Excel references keep changing | Excel Discussion (Misc queries) | |||
![Ken Warthen[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
