1

I am trying to copy-paste a range of cells to another location in my spreadsheet. The problem is that unlike cut-paste, when I use copy-paste I lose the original cell references. I was wondering if it is possible to create an artificial "cut-paste" option, but without deleting the original data, and with the ability to repeat the operation more than once.

I've found a partial solution to this problem by using the notepad or a VBA code to copy the formulas (as explained here). However, it still doesn't provide a complete solution, because I still want to have certain cells changed - for example, cells with SUM function should be updated so they calculate the sum of the newly pasted cells, and not the old cells. This is something the cut and paste option is capable of, but I couldn't find any parallel method to do that with copy-paste.

Any help will be much appreciated.

Community
  • 1
  • 1
Lev
  • 113
  • 1
  • 4
  • 13
  • have a little patient, work on your code, start small and simple, slowly and progressively until your code covers everything you need. Come to SO when you have a "specific" programming problem / error. – Rosetta Sep 17 '16 at 07:17
  • The problem is that you don't have your formula references setup properly. This might help you [Relative and Absolute Cell References](http://www.gcflearnfree.org/excel2013/relative-and-absolute-cell-references/1/) –  Sep 17 '16 at 07:24

1 Answers1

0

I think this may help you!, For example you have a formula at Sheet2!B4 like this:

=IF(B2>A4,Sheet1!B4,B2/B12)

And you want to copy/paste it to Sheet2!B5 and Sheet2!C5 -current sheet- and you hope it becomes (expected result):

=IF(B2>A5,Sheet1!B5,B2/B12)        '=> for B5 (B2,B12: no changes & A4>A5: just row changes)
=IF(B2>A6,Sheet1!C6,B2/B12)        '=> for C6 (B2,B12: no changes & A4>A6: just row changes)

But Excel do it like this:

=IF(B3>A5,Sheet1!B5,B3/B13)        '=> for B5
=IF(C4>B6,Sheet1!C6,C4/C14)        '=> for C6

In this case I change my formula to this:

=IF(INDIRECT("B2")>INDIRECT(CONCATENATE("A", ROW())),Sheet1!B5,INDIRECT("B2")/INDIRECT("B12"))

Note:

  • For any reference that should not change use INDIRECT("<refrence>").
  • For any partial changes you need to use INDIRECT("<refrence>") and generate <reference> separately by a function like ADDRESS().
shA.t
  • 16,580
  • 5
  • 54
  • 111