Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Excel (2007), when cells that contain absolute references (for example: $A$3) are copied, the absolute reference remains the same. This is by design, and the reason of using absolute references.

Problem -- However, sometimes I want copy a block of cells (that contain absolute and probably also relative references), and paste them with the absolute references shifted correctly for the new block. That is, I want the absolute references to behave like relative references when copying, but still be absolute references in the final copied result.

Example -- In the example screenshot, I want to copy the block A2:B3 downwards. When copied, I basically want to have the formula in B3 (=$A$3) changed so that it refers to the cell to the left of it, for example becoming =$A$11 when copied to B11, as in the bottom part of the screenshot.

example in Excel

Workaround -- I have found a workaround to do this, by:

  1. making a copy of the entire worksheet (ctrl-drag worksheet tab to new location),
  2. then cutting (ctrl-X) the relevant block of cells from the new worksheet
  3. pasting (ctrl-v) into the original worksheet.
  4. finally deleting the new, temporary worksheet (right-click worksheet tab and delete).

Question -- But this is too many actions for my taste. Is there an easier way (perhaps some Paste Special hidden option)?

share|improve this question
    
Why not just delete the second $ sign? Make cell B3 =$A3 instead of =$A$3. –  Kevin Anthony Oppegaard Rose Feb 27 '13 at 9:40
    
@Kevin: This is just a toy example. In the real case (some months ago) I had something like a 20x20 block, where I had a few 'configuration' cells which were in a fixed position (relative to the block). For constructing the block (ie. expanding from a single row to 20 rows ), it is the most convenient to work with absolute cell references. However, when I make a copy of the block (to see the result with other values for the configuration cells) in any direction, the absolute cells lead to the problem described in my post. –  Rabarberski Feb 27 '13 at 12:34
    
My guess is that you have tried the entering =$A3 in the cell then copying it and then Paste the Formula only in the target cell..... –  Darius Apr 3 '13 at 23:34

4 Answers 4

you could always try writing a macro for that. excel has a really nice macro recording tool that you could use too, and then just run it as needed (provided you make some changes first of course to the program)

dim firstLetter as String 
dim secondLetter as String 
dim firstNumber as integer 
dim secondNumber as integer 
dim firstReference as string 
dim secondReference as string 
dim contents as string 
firstLetter = inputbox("Where's the first column? (it's letter)") 
firstNumber = inputbox("And what's the first row? (just the number)") 
secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") 
secondNumber = inputbox("And what row? (the number.)") 
contents = range(firstletter + cstr(firstnumber)).formula 
range(secondletter + cstr(secondnumber)).formula = contents 
share|improve this answer
    
This isn't a recordable process. Obviously you can always write a custom macro to automate something but it would be more helpful to attempt to give some sample code. –  lori_m Mar 13 '12 at 14:37
    
dim firstLetter as String dim secondLetter as String dim firstNumber as integer dim secondNumber as integer dim firstReference as string dim secondReference as string dim contents as string firstLetter = inputbox("Where's the first column? (it's letter)") firstNumber = inputbox("And what's the first row? (just the number)") secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") secondNumber = inputbox("And what row? (the number.)") contents = range(firstletter + cstr(firstnumber)).formula range(secondletter + cstr(secondnumber)).formula = contents –  SUPER MARIO BROTHERS Mar 15 '12 at 12:21
    
oh good god that didn't carry over well at all from notepad. but yeah, something like that might work -- you MAY have to play around with it a bit but I think 'formula' is the cell's formula where as the r1c1 thing does that but maintains relative reference which is what we don't want to do. –  SUPER MARIO BROTHERS Mar 15 '12 at 12:22
    
I've tried to insert your code into the post, you can edit it from there. Hope this helps –  lori_m Mar 15 '12 at 12:56

If I try to follow the steps to cut and paste from a new worksheet I find that all references in formulas stay fixed when copying to the new location including relative references. In fact in Excel 2010 i find that after cutting and pasting formulas, the first row and column contain links to the old sheets but other rows and columns reference the new sheet which looks like a bug??

If you are wanting to copy a block of formulas keeping all references the same, you can press Ctrl+` (backquote) to show formulas and then copy and paste by clicking the icon on the Clipboard task pane (activate using small arrow on clipboard section of Home Tab). If this is not what you are trying to achieve, a simple example would help.

share|improve this answer
    
I've added an example with illustration. Hope that makes it clear. –  Rabarberski Mar 13 '12 at 13:56
    
That's clear, but your workaround does not achieve that result for me. I don't think there's an easy way to do this, I'd just do an edit replace for the absolute references i.e. replace $A$3 with $A$11. –  lori_m Mar 13 '12 at 14:32
    
strange you have that issue. Mine (Excel 2007) is working fine –  Rabarberski Mar 13 '12 at 14:52

I have been trying this macro (stored in Personal.xlsb and bound to shortcut key) to convert references to absolute before copying.

Sub ToAbsolute()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Sub ToRelative()
 Dim c As Variant
    Application.ScreenUpdating = False
    For Each c In Selection
        If (Not IsEmpty(c.Value)) Then
            c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlRelative, c)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
share|improve this answer

The following will work with less complexity then writing your own macro and achieves the final result.

Yes, I know I'm not using the Absolute cell reference but as shown in OP's example you don't need it.

Select range that you want to copy

enter image description here

Then from the drop down menu of Paste choose the Formulas option as shown here

enter image description here

This has been tested by me and works in Excel 2007 and 2010. Enjoy :)

share|improve this answer
1  
Doesn't work for me (Excel 2007). I think the reason it appears to work for you is because in the formula =$A7 only the column (A) is absolute, but the row (7) is relative. If you use =$A$7 you'll see it doesn't work. In my original example, it is actually the absolute row reference that IS important. –  Rabarberski Apr 4 '13 at 7:22
    
The question is, do you really, really (for really :D ) need the absolute reference. I found myself that the second I want to copy the absolute reference it appears I can get away with $ on either column or the row. –  Darius Apr 4 '13 at 15:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.