I Need to change reference sheet for all cells on a form

[Brent E]

Good afternoon,

I am copying a spreadsheet to make summary sheet and I need to change which
sheet the cells are referncing on my copy. Some of the cells refer
to for example, =PRIOR YEAR!B3, some of the cells use a command
to Round(PRIOR YEAR!B3/100), I would like to change all the references that
currently refer to "PRIOR YEAR" sheet and make them "CURRENT YEAR" sheet, so
my references would look like this =CURRENT YEAR!B3 and Round(CURRENT
YEAR!B3/100). I am curious if a paste special, or shortcut exists that can do
this quickly so I dont have to go in and manually change each cell. Any
suggestions? Thanks

Cordailly,

 

[Dave Peterson]

Edit|Replace springs to mind--but watch your typing.

I bet your formulas look more like:
='Prior year'!b3
(note the single quotes).

Another option would be to put the worksheet name that you want to use in A1
(say).

Then change your formulas to:

=indirect("'" & a1 & "'!B3")

Then you'd just have to change the value in A1 to update your formulas.