Skip navigation

alternative to Drag and Drop for "identity" copying cells?

514 Views 18 Replies Latest reply: Nov 11, 2013 2:16 PM by hotwheels 22 RSS
1 2 Previous Next
hotwheels 22 Level 1 Level 1 (0 points)
Currently Being Moderated
Nov 6, 2013 1:26 PM

hi all.


right now when i want an "identity copy" of a single cell i do an equation where i enter the equals size and i click in the cell i need. this maintains a link with the original cell.


right now i need multiple cells (like five in a row) to get "identity copied" to a set of five new destination cells.


is there a way to do this so that i don't have to /drag/ the origination selection all the way down my spreadsheet or is this the only way to do it?


i have about 800 rows to travel and i have to do this about thirty times and it is going to get really tiresome.


i tried copy paste but it just entered five cells with "0.00" for some reason likely having to do with it being a copy of the format of the cell but not the actual content...


TIA for any advice or terms or search words etc

MacBook Pro, OS X Mavericks (10.9)
  • Wayne Contello Level 6 Level 6 (12,660 points)



    It is time for you to download the manual and read the material in chapter six.


    This manual is still usaeble for understanding how to select cell, reference cells and copy and paste.  If is free and downloadable AND you can search it-- if you know the terms to search.  It is a valuable tool. 


    when they are contiguous in both the source and destination, you can select the one, then copy, then select the grounp and paste.


    In this example there are two tables:

    1) a source table

    2) s destination table


    Screen Shot 2013-11-06 at 3.29.54 PM.png

    in the destination table highlight cell A1 then type the "=" (equal sign) :

    Screen Shot 2013-11-06 at 3.30.01 PM.png

    now click the cell in the source table (A1):

    Screen Shot 2013-11-06 at 3.30.06 PM.png


    Now hit the return key.  Select cell A1, copy.  Now select the whole table (or the group of cells you want to reference in the Source table).  In this example select all.  To select all use the menu item "Edit > Select All":


    Screen Shot 2013-11-06 at 3.30.34 PM.png


    Now paste:

    Screen Shot 2013-11-06 at 3.30.39 PM.png


    If you only wanted to reference A1, A3, B4, B6, and B8 you would enter the formula in any cell you want-- I choose A1.  Then you select A1, copy, then hold the command key then click those cells, then paste:

    Screen Shot 2013-11-06 at 3.37.08 PM.png

  • Wayne Contello Level 6 Level 6 (12,660 points)



    cells are name using coordinates (a row and a column).  we specify which cell to select by saying the column first, then the row.


    I the example I posted I asked you to select cell A1-- that is... the cell at the intersection of column A row 1.


    you should say to yourself when entering formulas  (as you type the equal sign):

    "This cell equals"


    the select the combination of cells that combine through what ever mathematical (or formulaic) operations.


    Your formula is about the simplest there is.  "This cell equals this other cell"


    to do this:

    1) type the equal sign in the destinartion cell

    2) click the source cell

    3) hit enter



    That's it



    to fill that same formula into other cells reselect the destination cell (the one you just entered the formula in , then copy


    now select another cell(s), paste


    to select contiguous cell(s) click the starting cell, the hold the shift key while clicking the last cell


    to select non-contiguous cells click the first cell, the hold the command key while click the other cells you wish to add to the selection


    once the cells are selected paste

  • Wayne Contello Level 6 Level 6 (12,660 points)

    Notice how the row and column headers are highlighted:


    Cell C3 is selected:

    Screen Shot 2013-11-06 at 4.40.15 PM.png



    Cell F7 is selected:

    Screen Shot 2013-11-06 at 4.40.23 PM.png

  • Wayne Contello Level 6 Level 6 (12,660 points)



    I think what you want is called ABSOLUTE REFERENCING.  Normally when you copy a cell, then paste somewhere else the spreadheet program modifies the references based on the offset between the source and the destination.


    if you want to the group of references to always to refer to the EXACT same cells regardless of where they are pasted then you need absolute references



    In this case here is a single Source table and two destination tables:

    Screen Shot 2013-11-06 at 4.46.30 PM.png


    In the table "Destination 1" select cell A1 and type the "=", then click cell A1 in the table Source


    NOW.... click the menu for the cell reference (down facing arrow in the tagged reference on the right) and select absolute row and column).  you can do the same thing by tying a "$" before the row and before the column.  This will be a pain to copy and paste becuase it will not adjust the first time so we would have to type each address out.


    There is another way:

    so the formula is:

    A1=INDIRECT(ADDRESS(ROW(), COLUMN(), 1,1, "Source"))



    now select A1, then select A1 thu B9, paste



    now selelect A1 through B9 in the table Destination 2, now paste


    mow try changing a value in the source table.

  • Wayne Contello Level 6 Level 6 (12,660 points)



    you are talking about within the same table?



    so you mean this:


    select cell C7

    type "=" then click A3

    hit the enter key

    Screen Shot 2013-11-06 at 4.59.09 PM.png

    Screen Shot 2013-11-06 at 4.59.15 PM.png


    now select C7 again, how click and hold on the little circle at the bottom right coner of the selected cell and drag to the right as needed (this fills the formula across)

  • Wayne Contello Level 6 Level 6 (12,660 points)



    1) click the destination cell (A7)

    2) type "="

    3) click cell A3

    4) type enter

    5) select cell A7

    6) grab (click and hold) the little circle at the bottom right corner of the selected cell, then drag to the right as needed

    7) release the mouse button


    that's it

    Screen Shot 2013-11-06 at 5.42.08 PM.png

1 2 Previous Next


More Like This

  • Retrieving data ...

Bookmarked By (0)


  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.