CODEWITHSUNDEEP
referenceapache-poiformularenaming
8xomaster
8xomaster

Reputation: 175

In POI, how to rename a sheet without changing references to it?

I have SheetA, that SheetX has formula references to. Now I want to replace SheetA by SheetB which is similar (but has additional rows). To do that I rename SheetA to OldSheetA, then rename SheetB to SheetA.

This did not work because formula references on SheetX now point to OldSheetA instead of SheetA:

='SheetA'!G5  --becomes--> ='OldSheetA'!G5

How could I keep references pointing to SheetA ?

Upvotes: 2

Views: 584

Answers (2)

8xomaster
8xomaster

Reputation: 175

I did not find a solution using POI but this simple formula fix does the trick. Use INDIRECT() function in formulas to refer to SheetA so that they will not be affected by sheet renaming.

Instead of formula like this:

=SheetA!B2

Change to this:

=INDIRECT("SheetA!B2")

Upvotes: 0

jmarkmurphy
jmarkmurphy

Reputation: 11473

The only way to keep references pointing to SheetA is to avoid renaming it.

I suspect that the only choices you will have are:

  1. To repoint your references in SheetX after all the renaming is done.
    -or-
  2. To copy your data from SheetA to a new sheet, then create SheetB's data in place on SheetA

Upvotes: 1

Related Questions