Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas with previous workbook references (excel 2000)

    I want to get a worksheet from one workbook to another, but I do not want the old workbook name in the formulas.


    Here is an example of a formula before I copy a sheet:

    =IF(ISERROR(VLOOKUP(INDEX(Values!$C$2:$C$115,MATCH ($A4,Values!$A$2:$A$115,0)),HTData!$A$2:$BA$46,3,0 )),"0",(VLOOKUP(INDEX(Values!$C$2:$C$115,MATCH($A4 ,Values!$A$2:$A$115,0)),HTData!$A$2:$BA$46,3,0)))/86400

    and after I paste it into a different workbook:

    =IF(ISERROR(VLOOKUP(INDEX('[IAMProd-MTD.xls]Values'!$C$2:$C$115,MATCH($A4,'[IAMProd-MTD.xls]Values'!$A$2:$A$115,0)),'[IAMProd-MTD.xls]HTData'!$A$2:$BA$46,3,0)),"0",(VLOOKUP(INDEX('[IAMProd-MTD.xls]Values'!$C$2:$C$115,MATCH($A4,'[IAMProd-MTD.xls]Values'!$A$2:$A$115,0)),'[IAMProd-MTD.xls]HTData'!$A$2:$BA$46,3,0)))/86400

    Notice the [IAMProd-MTD.xls].....The formula keeps bringing over the old workbook name, even though it isnt relevant now because I have a related sheet for the formula to reference in my other workbook. . I want to copy the whole sheet because it is extensive with these kinds of formulas, but no matter what I do, it brings over the previous workbooks name as a reference.

    How can I copy over sheets that take the formulas as-is so I can plug and play in my new workbook?

  2. #2
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas with previous workbook references (excel 2000)

    Here is what I considered doing- replacing the old workbook reference with a blank using find/replace. I have many sheets, however, and thought there must be a better way.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formulas with previous workbook references (excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 09-Oct-05 05:21. Added PS)</P>1) you can copy over the sheet with the formulas and at the same time copy over the sheets that it references in the formulas. You get the links, since the formulas you reference in the copied sheet, now refer to cells in another workbook (by design). If you copy them both at the same time, excel knows the links to the other sheet are also going to the new workbook.

    2) If you want to copy the formulas in the 1 sheet and not have them linked to the original, but "changed" to link to the new book, copy as you have done the existing sheet (which will create the links). Then go to edit - links <Change Source> and select the current workbook. This will modify all the formula links from the original workbook to the new workbook. [Note: this requires that the sheet names exist in the new workbook]

    Steve

    PS. You could also use INDIRECT in all your formulas, but I do not recommend this. They will copy over alright, but they will not longer expand/contract if you insert or delete columns or rows. It will also not auto update with changed sheet names, etc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •