+ Reply to Thread
Results 1 to 4 of 4

Copy/Paste without changing location references

  1. #1
    Tom
    Guest

    Copy/Paste without changing location references

    In Excel 2003, I have a multiple worksheet spreadsheet that uses cells from
    one worksheet to another worksheet. For example, Worksheet "Data" (tabname
    ="Data") has several columns of numeric data. Worksheet "Ratios"
    (tabname="Ratios") has only TWO entries of numeric data, B1 and B2.
    When I define a formula in "Data" referencing "Ratios" and then copy the
    formula down the column in "Data" the referenced cells in "Ratios" get
    incremented in the formula/statement I'm using. For example, my formula in
    "Data" could say "=if(C1<Ratios!B1,"Red",if(C1<Ratios!B2,"Green","Yellowl"))"
    Now if I copy/paste this from the "Data" Worksheet, the Ratios!B1 and
    Ratios!B2 get incremented for each row that I paste the calculation in the
    "Data" Worksheet.
    Is there a simple solution to KEEPING the reference cells in the "Ratios"
    Worksheet as "Ratios!B1" and "Ratios!B2" as the formulas are copy/pasted?
    That is, keep the references to B1 and B2 in the "Ratios".
    Extrapolating this, how can I keep ALL components in formulas in a
    copy/paste from incrementing?
    TIA,
    Tom

  2. #2
    Registered User
    Join Date
    01-20-2004
    Location
    Western NY
    Posts
    99
    Perhaps I have misunderstood what you are trying to do, but it seems like you can use the Excel "standard" for nonincrementing rows and cols, i.e., use a "$" before the row and col indicators? For example, if I have a cell with contents =Sheet1!F29 and copy/paste it down the current row, the next cell with be =Sheet1!F30, etc. But if the cell contents is =Sheet1!F$29, then every cell to which it is pasted has the same formula, i.e., =Sheet1!F$29. Similarly, =Sheet1!F$29 would keep the formula the same if pasted to the right. And =Sheet1!$F$29 would keep the formula the same for any paste.

    The process does require several step, i.e., a first paste link from the source to the first cell or row or col of the target, a tweak adding $ and a final paste.

  3. #3
    Richard Reye
    Guest

    RE: Copy/Paste without changing location references

    Your formula could be re-written like this

    =if(C1<Ratios!$B$1,"Red",if(C1<Ratios!$B$2,"Green","Yellowl"))

    The '$' keeps the Column or Row reference that it preceeds constant. These
    can be added manually or by using F4 when entering in the formula.



    "Tom" wrote:

    > In Excel 2003, I have a multiple worksheet spreadsheet that uses cells from
    > one worksheet to another worksheet. For example, Worksheet "Data" (tabname
    > ="Data") has several columns of numeric data. Worksheet "Ratios"
    > (tabname="Ratios") has only TWO entries of numeric data, B1 and B2.
    > When I define a formula in "Data" referencing "Ratios" and then copy the
    > formula down the column in "Data" the referenced cells in "Ratios" get
    > incremented in the formula/statement I'm using. For example, my formula in
    > "Data" could say "=if(C1<Ratios!B1,"Red",if(C1<Ratios!B2,"Green","Yellowl"))"
    > Now if I copy/paste this from the "Data" Worksheet, the Ratios!B1 and
    > Ratios!B2 get incremented for each row that I paste the calculation in the
    > "Data" Worksheet.
    > Is there a simple solution to KEEPING the reference cells in the "Ratios"
    > Worksheet as "Ratios!B1" and "Ratios!B2" as the formulas are copy/pasted?
    > That is, keep the references to B1 and B2 in the "Ratios".
    > Extrapolating this, how can I keep ALL components in formulas in a
    > copy/paste from incrementing?
    > TIA,
    > Tom


  4. #4
    Tom
    Guest

    RE: Copy/Paste without changing location references

    Thanks for the input. I've never used the "$" approach! You're input is
    greatly appreciated.

    "Richard Reye" wrote:

    > Your formula could be re-written like this
    >
    > =if(C1<Ratios!$B$1,"Red",if(C1<Ratios!$B$2,"Green","Yellowl"))
    >
    > The '$' keeps the Column or Row reference that it preceeds constant. These
    > can be added manually or by using F4 when entering in the formula.
    >
    >
    >
    > "Tom" wrote:
    >
    > > In Excel 2003, I have a multiple worksheet spreadsheet that uses cells from
    > > one worksheet to another worksheet. For example, Worksheet "Data" (tabname
    > > ="Data") has several columns of numeric data. Worksheet "Ratios"
    > > (tabname="Ratios") has only TWO entries of numeric data, B1 and B2.
    > > When I define a formula in "Data" referencing "Ratios" and then copy the
    > > formula down the column in "Data" the referenced cells in "Ratios" get
    > > incremented in the formula/statement I'm using. For example, my formula in
    > > "Data" could say "=if(C1<Ratios!B1,"Red",if(C1<Ratios!B2,"Green","Yellowl"))"
    > > Now if I copy/paste this from the "Data" Worksheet, the Ratios!B1 and
    > > Ratios!B2 get incremented for each row that I paste the calculation in the
    > > "Data" Worksheet.
    > > Is there a simple solution to KEEPING the reference cells in the "Ratios"
    > > Worksheet as "Ratios!B1" and "Ratios!B2" as the formulas are copy/pasted?
    > > That is, keep the references to B1 and B2 in the "Ratios".
    > > Extrapolating this, how can I keep ALL components in formulas in a
    > > copy/paste from incrementing?
    > > TIA,
    > > Tom


+ 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