Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Absolute cut and paste (2000 SR-1)

    This is probably a simple question, but I don't know the answer. How can I copy and paste a cell as an absolute reference not a relative reference? Is there some key you can hold down when you paste?

    Thanks in advance,
    Georgette

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute cut and paste (2000 SR-1)

    What do you mean by "absolute reference?" Do you want to convert a relative reference in the copied cell like A1 to an absolute reference like $A$1, or do you just want a relative reference in the copied cell like A1 to stay A1 when pasted into the destination cell? Or did you mean something else?

    If you want the relative reference in the copied cell to remain the same when pasted, then you can do the following:

    1- Select the cell to be copied.

    2- Hilite the formula in the formula bar and then either right click and select copy or do an edit copy.

    3- Click on the X to the right of the formula bar to remove the selection.

    4- Select the cell where you want to paste the formula.

    5- Click in the formula bar and then paste and hit enter.

    You should not have the formula duplicated without relocating the references.

    If you want to convert some or all of the references to absolute $A$1 type references, then repeat the above to paste the formula. Then do the following:

    1- Select the cell where the pasted formula is.

    2- Select the references in the formula that you want to make absolute in the formula bar, or the whole formula if you want all references to be absolute.

    3- Press F4 until the references are the way you want them.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute cut and paste (2000 SR-1)

    I have a cell with a sum formula it [cell B10 contains formula sum(A1:A10]. I want to copy that cell and paste it as $B$10 to another cell (say E80). It there any easy was to do this (keystroke combination)?
    gg

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute cut and paste (2000 SR-1)

    Maybe the easy way to do it is to name the "one-cell" range?
    gg

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute cut and paste (2000 SR-1)

    Well, I don't see any way short of writing a macro that you can get Excel to convert a relative reference to a range of cells to an absolute reference to a single cell. The closest that I could come would be to name the range that you want to sum, or unless what you are trying to do is rally a lot more complex than you are describing here, just retype the formula in the new cell.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute cut and paste (2000 SR-1)

    I think that the "name the range" technique is the way to go. I was not looking for anything complex... rather I wanted a simple keystroke solution.

    Thanks for your thoughtful input! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    gg

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Absolute cut and paste (2000 SR-1)

    How about using th F4 function key to convert your relative references to absolute ones?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Absolute cut and paste (2000 SR-1)

    Range name is the best solution (expect when you -don't- want the name to be absolute!), but FYI "Ctrl-' " (Control-Apostrophe) will make an absolute copy of the cell immediately above it, which you could then drag elsewhere. Nobody mentioned that you can also copy the formula "text" from the formula bar or the cell in in-cell edit mode, open the target cell in edit mode and paste the exact same formula. Hardly a one or two keystroke solution, however; maybe four keystrokes?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Star Lounger
    Join Date
    Sep 2001
    Location
    Northern Virginia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute cut and paste (2000 SR-1)

    CTRL-' what a neat trick!!

    The range solution works best for my current situation, but I will keep your solution in mind for other circumstances.

    Thanks to you all--Legare, WebGenii and JohnBF--for your creative solutions!
    gg

Posting Permissions

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