CODEWITHSUNDEEP
excelexcel-formula
sp00m
sp00m

Reputation: 48807

Updating absolute references while copying a formula

I've got a huge table with a lot of formulas, and some of them refer to cells in an absolute way (i.e. $C$3 instead of C3).

How can I now duplicate this table in the same sheet, while updating the value of the absolute references, but keeping them still absolute (i.e. $C$3 should become $I$3)?

Rewriting each formula while removing any existing $ would be such an annoying work...

Upvotes: 0

Views: 509

Answers (3)

Freekh
Freekh

Reputation: 3

The easiest solution when copying large areas filled with folumas, relative and absolute references, is indeed the CUT and PASTE.

You don't need to save it in an seperate file though, just copy the worksheet your formulas are in, and then cut and paste from there.

It is a very annoying workaround if you need to copy the formulas a lot... But I doubt there is a better way...

Upvotes: 0

Dopeybob435
Dopeybob435

Reputation: 138

The power of the find/replace command:

Highlight the range you need to edit (duplicate table) will limit the purview of the command

Find $C$ Replace $I$

Upvotes: 1

sp00m
sp00m

Reputation: 48807

I found one solution, but there must/should be something more convenient.

  1. Copy/paste your whole XLS file to create a new temporary file my file - Copy.xls
  2. Open the copied file and cut the table you want to copy
  3. Paste it in the original file where you want it to be
  4. Delete the temporary file

Still open to any other trick or tip to do so, in a less annoying way...

Upvotes: 0

Related Questions