Jivejive
Level 1 (4 points)
iWork

Q: How to move cells with formulas without absolute references and still retain cell references

I know about selecting absolute references and so forth, but sometimes I would like to be able to copy a group of cells with formulas and paste them elsewhere while retaining the original cell references. I.e., multiple cells with a formula referring to "A1" continue referring to "A1" even when I copy the cells elsewhere.

 

Is there a copy and paste modification (e.g., command+option+C or something?) so that I can copy the same as in Excel, which doesn't change the cell references when you copy a formula elsewhere?

 

Thanks!

MacBook Pro, null

Posted on Sep 9, 2016 2:27 PM

by t quinn,Apple recommended
t quinn t quinn
Level 5 (5,012 points)
Mac OS X
A:

Hi Jivejive,

 

I think Ian has you covered with a slight modification:

 

Select your column/cells

Copy your column/cells

double click your cell and drag to new location

click in the original first cell(or column) and paste.

 

This will give you two identical columns/cells.

 

quinn

Posted on Sep 12, 2016 7:18 AM

Close
Jivejive

Q: How to move cells with formulas without absolute references and still retain cell references

  • All replies
  • Helpful answers

  • by t quinn,Helpful

    t quinn t quinn Sep 9, 2016 6:16 PM in response to Jivejive
    Level 5 (5,012 points)
    Mac OS X
    Sep 9, 2016 6:16 PM in response to Jivejive

    Hi jivejive,

     

    I don't know of any way to do what you want with a group of cells. I have done it with single cells by copying the function from within the formula inspector and then pasting into the inspector in the new location.

     

    quinn

  • by Jivejive,

    Jivejive Jivejive Sep 9, 2016 6:16 PM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 9, 2016 6:16 PM in response to t quinn

    Thanks T Quinn. I know what you're talking about - I'm just a bit blown away that there isn't a shortcut to change the copy behavior - especially when excel is Numbers' main competitor and the default copy behavior is what I'm asking about. I understand Numbers being programmed differently, just find it really limiting (and inconvenient) to not have an option.

  • by Yellowbox,Helpful

    Yellowbox Yellowbox Sep 12, 2016 6:59 AM in response to Jivejive
    Level 6 (10,455 points)
    Mac OS X
    Sep 12, 2016 6:59 AM in response to Jivejive

    Hi Jivejive,

     

    Select the cells that you want to move.

    Screen Shot 2016-09-12 at 3.04.53 PM.png

    A 'hard' click (I use the bottom left corner of the trackpad) makes the selection appear to 'lift' off the page.

    Drag and drop to where you want.

    Screen Shot 2016-09-12 at 3.09.18 PM.png

    The formulas continue to refer to column A.

     

    Regards,

    Ian.

  • by Jivejive,

    Jivejive Jivejive Sep 12, 2016 7:06 AM in response to Yellowbox
    Level 1 (4 points)
    iWork
    Sep 12, 2016 7:06 AM in response to Yellowbox

    Hi Ian,

     

    Thanks. This, too, is something I frequently use. The issue with moving with a hard click is that it moves the cells and equations and doesn't copy them. I sometimes create equations that I want to then also use elsewhere while changing some variables/references and not others, and I don't necessarily know ahead of time which cells/equations I'll want to maintain absolute references once I've copied them to new locations. As such it isn't useful to select absolute references ahead of time, and it is quite timely to do so in tens (or more) of cells after copying.

     

    Thus im looking for a copy behavior modification that mimics Excel's copy behavior -  I prefer Numbers in many respects (iCloud sync is exceptional, e.g., and the live charts are very useful for quickly looking at data)... but in this one functional spot I'm frequently annoyed when I try to do more significant equations work.

  • by t quinn,Apple recommended

    t quinn t quinn Sep 12, 2016 7:18 AM in response to Jivejive
    Level 5 (5,012 points)
    Mac OS X
    Sep 12, 2016 7:18 AM in response to Jivejive

    Hi Jivejive,

     

    I think Ian has you covered with a slight modification:

     

    Select your column/cells

    Copy your column/cells

    double click your cell and drag to new location

    click in the original first cell(or column) and paste.

     

    This will give you two identical columns/cells.

     

    quinn

  • by Jivejive,

    Jivejive Jivejive Sep 12, 2016 7:31 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 12, 2016 7:31 AM in response to t quinn

    Ah yes! That'll do. A little more unwieldy but not too bad. I'll take it for now - though I'd love to here from. Numbers development team member if there is any shortcut for this kind of behavior.

     

    thanks!

    Jivan

  • by t quinn,

    t quinn t quinn Sep 12, 2016 7:49 AM in response to Jivejive
    Level 5 (5,012 points)
    Mac OS X
    Sep 12, 2016 7:49 AM in response to Jivejive

    Hi Jivan,

     

    You might want to go to Menu>Numbers>Provide Numbers Feedback. This could be a useful feature.

     

    quinn

  • by Jivejive,

    Jivejive Jivejive Sep 12, 2016 7:53 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 12, 2016 7:53 AM in response to t quinn

    Yeah I'll do that. Thanks again.

    J