Editing multiple formulas in a sheet

    Hi,


    I have a sheet in which every cell is a formula. The problem is that the formula references a wrong sheet. Is there a way to edit the formula without going into each cell.


    Thanks!


    J

    Re: Editing multiple formulas in a sheet


    Hi there


    A find & replace all command should do the trick


    (Edit>>Replace)


    Pangolin

    Re: Editing multiple formulas in a sheet


    Hi Pangolin,


    I tried doing edit and replace. It tries to look for a file and then hangs. Any idea what's going on? Thanks!


    J

    Re: Editing multiple formulas in a sheet


    I created a formula:=CT!H26/CT!$BA26 on one of my sheets. I copied this sheet on the same spreadsheet. I need to replace the reference to CT! worksheet in the above formula with NY-NJ! worksheet. Everything else remains the same.


    When I do a find and replace, a window with 'File not found' message crops up.


    Thanks!


    Jerry

    Re: Editing multiple formulas in a sheet


    Check that there are not external links:
    Edit > Links
    If there is a reference to an old (possibly deleted) version of the file, click "change source" and reference the latest (active) file.
    Now find and replace should work.


    Note, select range before running find and replace if there are a lot of links on a page, and it helps to be speciifc - i.e. search and replace "CT!" for, say, "NY!" including the ! but not the "".
    This will stop you replacing text, and other occurences of CT in the workbook.


    Hope that helps,
    Mike

    Re: Editing multiple formulas in a sheet


    Pangolin/DR,


    I got it. I had to put the single quotes around 'NY-NJ'. Also, instead of selecting the whole sheet and doing a relplace all, I selected a single cell and did a replace all. This seemed to help.


    Regards,


    J

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login