+ Reply to Thread
Results 1 to 10 of 10

Copy Formula That References Another Sheet

  1. #1
    Forum Contributor
    Join Date
    02-15-2006
    Posts
    107

    Copy Formula That References Another Sheet

    How can I copy a formula that references another sheet to a new file"

    file name: sales
    sumif(sheet1 a:a)

    When I copy this formula to a new file the formula is:
    sumif(c:/mydocs.sales 'sheet1! a:a)

    I just want the formula to be:
    sumif(sheet1 a:a)

    Is this possible?

  2. #2
    Dave Peterson
    Guest

    Re: Copy Formula That References Another Sheet

    Copy from the formula bar and paste into the formula bar.

    Or change the formula to text
    select your range of formulas (more than one cell)
    edit replace
    what: = (equal sign)
    with: $$$$$
    replace all

    copy and paste

    and then change the strings back to formulas (edit|replace--in both worksheets)

    JR573PUTT wrote:
    >
    > How can I copy a formula that references another sheet to a new file"
    >
    > file name: sales
    > sumif(sheet1 a:a)
    >
    > When I copy this formula to a new file the formula is:
    > sumif(c:/mydocs.sales 'sheet1! a:a)
    >
    > I just want the formula to be:
    > sumif(sheet1 a:a)
    >
    > Is this possible?
    >
    > --
    > JR573PUTT
    > ------------------------------------------------------------------------
    > JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
    > View this thread: http://www.excelforum.com/showthread...hreadid=513310


    --

    Dave Peterson

  3. #3
    vezerid
    Guest

    Re: Copy Formula That References Another Sheet

    Double-click the cell to edit. Copy the formula from the formula bar,
    i.e. copy the text of the formula and paste it.

    HTH
    Kostis Vezerides


  4. #4
    Forum Contributor
    Join Date
    02-15-2006
    Posts
    107
    Thanks that worked, but not for an array formula where I commit the formula by hitting ctrl shift enter.

    Is there a way to copy an array formula that references a sheet?

  5. #5
    Forum Contributor
    Join Date
    02-15-2006
    Posts
    107
    Quote Originally Posted by Dave Peterson
    Copy from the formula bar and paste into the formula bar.

    Or change the formula to text
    select your range of formulas (more than one cell)
    edit replace
    what: = (equal sign)
    with: $$$$$
    replace all

    copy and paste

    and then change the strings back to formulas (edit|replace--in both worksheets)

    JR573PUTT wrote:
    >
    > How can I copy a formula that references another sheet to a new file"
    >
    > file name: sales
    > sumif(sheet1 a:a)
    >
    > When I copy this formula to a new file the formula is:
    > sumif(c:/mydocs.sales 'sheet1! a:a)
    >
    > I just want the formula to be:
    > sumif(sheet1 a:a)
    >
    > Is this possible?
    >
    > --
    > JR573PUTT
    > ------------------------------------------------------------------------
    > JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
    > View this thread: http://www.excelforum.com/showthread...hreadid=513310


    --

    Dave Peterson

    What about an array formular?

  6. #6
    Dave Peterson
    Guest

    Re: Copy Formula That References Another Sheet

    You could use code...

    Option Explicit
    Sub testme()

    Dim FromCell As Range
    Dim ToCell As Range

    Set FromCell = Workbooks("book2.xls").Worksheets("sheet1").Range("a1")
    Set ToCell = Workbooks("Book1.xls").Worksheets("sheet1").Range("a1")

    If FromCell.HasFormula Then
    If FromCell.HasArray Then
    ToCell.FormulaArray = FromCell.FormulaArray
    Else
    ToCell.Formula = FromCell.Formula
    End If
    End If
    End Sub




    JR573PUTT wrote:
    >
    > Dave Peterson Wrote:
    > > Copy from the formula bar and paste into the formula bar.
    > >
    > > Or change the formula to text
    > > select your range of formulas (more than one cell)
    > > edit replace
    > > what: = (equal sign)
    > > with: $$$$$
    > > replace all
    > >
    > > copy and paste
    > >
    > > and then change the strings back to formulas (edit|replace--in both
    > > worksheets)
    > >
    > > JR573PUTT wrote:
    > > >
    > > > How can I copy a formula that references another sheet to a new

    > > file"
    > > >
    > > > file name: sales
    > > > sumif(sheet1 a:a)
    > > >
    > > > When I copy this formula to a new file the formula is:
    > > > sumif(c:/mydocs.sales 'sheet1! a:a)
    > > >
    > > > I just want the formula to be:
    > > > sumif(sheet1 a:a)
    > > >
    > > > Is this possible?
    > > >
    > > > --
    > > > JR573PUTT
    > > >

    > > ------------------------------------------------------------------------
    > > > JR573PUTT's Profile:

    > > http://www.excelforum.com/member.php...o&userid=31587
    > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=513310
    > >
    > > --
    > >
    > > Dave Peterson

    >
    > What about an array formular?
    >
    > --
    > JR573PUTT
    > ------------------------------------------------------------------------
    > JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
    > View this thread: http://www.excelforum.com/showthread...hreadid=513310


    --

    Dave Peterson

  7. #7
    Forum Contributor
    Join Date
    02-15-2006
    Posts
    107
    How do I use this code? Not familiar with code..............

  8. #8
    Dave Peterson
    Guest

    Re: Copy Formula That References Another Sheet

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    JR573PUTT wrote:
    >
    > How do I use this code? Not familiar with code..............
    >
    > --
    > JR573PUTT
    > ------------------------------------------------------------------------
    > JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
    > View this thread: http://www.excelforum.com/showthread...hreadid=513310


    --

    Dave Peterson

  9. #9
    Forum Contributor
    Join Date
    02-15-2006
    Posts
    107
    Code is Macro, yes I know how to record and run a macro, thanks for the answer.

  10. #10
    Forum Contributor
    Join Date
    02-15-2006
    Posts
    107
    Quote Originally Posted by Dave Peterson
    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    JR573PUTT wrote:
    >
    > How do I use this code? Not familiar with code..............
    >
    > --
    > JR573PUTT
    > ------------------------------------------------------------------------
    > JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
    > View this thread: http://www.excelforum.com/showthread...hreadid=513310


    --

    Dave Peterson
    I figured out a better way:

    Highlight source worksheet column,
    click copy
    go to recipient worksheet and paste
    Go to Edit Links, change source worksheet to recipient worksheet,
    click ok.

    Works great...............thanks again for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1