1

i have a large financial spreadsheet with a ton of formulae. i'd like to duplicate a row such that it retains the same formula and coordinates as the source row (basically duplicate a summary row and still have it be the same summary):

start with:

[448]   =J425+J441  =K425+K441  =L425+L441

end with:

[447]   =J425+J441  =K425+K441  =L425+L441
[448]   =J425+J441  =K425+K441  =L425+L441

a simple copy paste automatically advances each of the coordinates, and ends up pointing to the wrong spot.

Arjan
  • 31,163
kolosy
  • 119
  • 2
    Is this something you could address by using absolute references? http://www.cpearson.com/excel/relative.aspx =$L$425+$L$441. – Zoredache Aug 08 '12 at 00:06
  • not really - i'm fine with how the summary row is constructed, i just want to duplicate it. if i was building it from scratch, i could make it absolute, but that has its own issues, and i don't want to have to go back and make those changes – kolosy Aug 08 '12 at 00:10
  • Aside from copy-pasting the contents of each cell, I'm not sure this is possible –  Aug 08 '12 at 02:43
  • 1
    @kolosy why exactly isn't using absolute addresses a solution? It seems that you're trying to work around a problem with your formula, instead of addressing that problem directly. – Dmitry Grigoryev Dec 18 '15 at 08:16
  • Because this is being done to a large, existing spreadsheet. Making the references absolute requires redoing the existing summary row, which is as time-consuming as duplicating it by hand. I was looking for a way to automate the process; it doesn't appear to exist. – kolosy Dec 18 '15 at 17:16

8 Answers8

4

You can copy the cells in question and then paste link. I know this works in Excel 2010 but not sure about Excel Mac 2011. The paste link will make all the cell references absolute.

enter image description here

wbeard52
  • 3,447
  • this isn't exactly what i'm looking for. it just creates cells that point to the original cell (and they aren't absolute). the effect is similar - a row with the same values, but i need the formulae duplicated, not references to them – kolosy Aug 09 '12 at 03:40
  • VBA is your only solution – wbeard52 Aug 09 '12 at 04:12
1

This is easy if you're open to using VBA. The following procedure copies the formula from each cell in the source range to the destination, maintaining the references without changing to absolute references.

Sub CopyRowPreserveReferences()
    Dim rng As Range
    Dim cell As Range
    Dim iOffset As Integer

    iOffset = 1 ' this will copy the row 1 row down, set it to -1 if you
                ' want to copy to the row above
    Set rng = Range("a5:i5") ' set this to range you want to copy
    For Each cell In rng
        cell.Offset(iOffset).Formula = cell.Formula
    Next
End Sub
Jon Crowell
  • 2,296
1
  1. Click on "Show Formulas".  (In Excel 2007, this is on the "Formulas" tab, in the "Formula Auditing" group.)
  2. Save as CSV.
  3. Edit the CSV file (e.g., with Notepad) and make a copy of the row.
  4. Open the CSV in Excel.
  5. Since this will have stripped off formatting and other features, you might want to copy the new row back into your original XLS(X) file.
Scott - Слава Україні
  • 21,717
0

You want this:

  A 
1 hello
2 =A1
3 =A1

where A2 was the original cell with the formula, and A3 is the new cell

This should give:

  A
1 hello
2 hello
3 hello

as output.

So, click cell A2. then click the formula bar, and highlight the formula. Copy it. Now press escape.

Click cell A3. Click the formula bar and paste.

Hmm... if this is not the best solution, then I didn't understand the question :)

Arjan
  • 31,163
Ender
  • 1
  • 2
    Now imagine the row has hundreds of columns; that's a lot of copy/paste then. – Arjan Dec 18 '15 at 07:56
  • yes, that's annoying isn't it? How about this: assuming that all of your references are relative (eg A1 not $A$1) then you can do as wbeard says i.e.
    1. paste link across 100's of cells. 2) But then, go into each formula (F2) 3) then press SHIFT-HOME or SHIFT-END to highlight the forumla, 4) then press F4 3 times to toggle the refernces from $A$1 to A1 again. Still a Little painful, but...
     – Ender Feb 15 '16 at 09:05
0
  • Select the row you want
  • Right Click the row
  • Copy the row
  • Right click the same row again
  • Click "insert copied cells"
Kalle Richter
  • 2,272
unknown
  • 1
0

I found another answer:

  1. Replace all "=" characters with "#" characters (in your summary row).
  2. Copy and paste the row.
  3. Change the "#" characters back to "=" characters.

Disclosure: I don't entirely understand why this works. I have gotten it to work (in Excel 2007), but I haven't tested it exhaustively.

Community
  • 1
Scott - Слава Україні
  • 21,717
0

This can be done in a relatively straightforward manner in three steps:

  1. Make sure you have some blank rows to work in
  2. Copy the row [448] up to row [447] ; this sets all the row refs 1 less than they should for row 447
  3. Move the row [447] up to row [446] ; the row refs are still one less than they should be
  4. Copy the row [446] down to row [447]; the row refs are now incremented back up by 1

At this point your row [447] should now be a clone of row [448]

DACN
  • 1
0

Use the "Offset" function to reference other cells. This will retain the reference to the cell relative to your new cell.

Suat
  • 1