Forum Discussion

TerraBruns's avatar
TerraBruns
Copper Contributor
Jan 26, 2020

Excel Workbooks changing source

I have created a copy of worksheets in a workbook, and saved it as my new workbook. The issue is, it still has the original workbook in the formulas for numerous cells. How do I change this?
excel
Formulas and Functions
Charla74's avatar
Charla74
Iron Contributor
Jan 26, 2020
You can copy / paste values if you don’t need to maintain any formulae from the source data, otherwise select all data in the pasted sheet and use find & replace (Ctrl+H)....find the text linking to the old worksheet and leave the replace with field blank - click replace all.
TerraBruns's avatar
TerraBruns
Copper Contributor
Jan 26, 2020
Now I get a dialogue box that says that sheet (current sheet name) is not found in (current sheet name) and wants to know where I would like to put it in the order of my other sheets...
  • TerraBruns's avatar
    TerraBruns
    Copper Contributor
    Jan 26, 2020

    Okay, so I have been on one worksheet 'replacing all' choosing the source workbook, then clicking the worksheet I want it to use, and I have been doing the same sheet for 35 minutes...is it feasible that I would have to do this? Step 1:

     Step 2:

    Step 3

    • mathetes's avatar
      mathetes
      Silver Contributor
      Jan 26, 2020

      TerraBruns 

       

      Without seeing the actual sheets you're working with, it's hard to be really specific or even appropriate in our suggestions.

       

      One thought that occurs to me--that I've used under similar circumstances--is wherever possible to simple enter a correct new formula in one of the cells and then copy it throughout the rest of the worksheet. That may not work--depending on how the sheet is organized. If you have distinctive formulas in all those columns, for example, then you need to make separate and distinct corrections.

       

      Is it possible to actually upload a sample of the actual sheet?

      • TerraBruns's avatar
        TerraBruns
        Copper Contributor
        Jan 27, 2020

        I figured it out. I had saved a copy of the original, but then worked in the copy, so it was pulling from the original. I should have been working in the original, so I am starting over now. Thank you for all of your help, mathetes Charla74  

    • Charla74's avatar
      Charla74
      Iron Contributor
      Jan 26, 2020
      TerraBruns if you’re just removing links to the source data (where it was copied from) - using the find / replace - just select the entire sheet, then input the full path / file name into the ‘find’ box ‘C:\Users\DELL\.......di terra.xlsx] , leave the replace box empty and select replace all. You will need to do this in each sheet but the details should remain in the find / replace dialogue box.
      • Charla74's avatar
        Charla74
        Iron Contributor
        Jan 26, 2020
        In fact, I believe there may be a check box in the find / replace for all sheets ( working from memory here)...
  • Charla74's avatar
    Charla74
    Iron Contributor
    Jan 26, 2020
    Looking at the image you just posted, try taking out the full path, ‘C:Dell\Users\.......xlsm!]

Resources