How to automatically change the referenced worksheet name in all formulas?

[steven.cole]

Hi,

I have a worksheet (call it "formulas") that has a series of formulas
that work on data from another worksheet ("data 1"). I would like to
write a VBA function that automatically updates all the formulas that
reference the "data 1" worksheet on the "formulas" worksheet, to
reference a new data sheet ("data 2").

"Data 1" and "Data 2" are identical in structure, they just have
slightly different data. Therefore the cell references can remain the
same, it is just the worksheet reference that needs to change.

Please could someone tell me what the best way to do this is?

Thanks in advance,
Steve

 

[147man]

You can do it manually with a search and replace just highlight all the
cells and press Ctrl + H, treat with care as if you are not specific
enough in what you search for you could get unexpected results.

I.e. to take your example replace "Data 1" with "Data 2" if you replace
"1" with "2" then the references elsewhere in the formulae will become
corrupted.

 

[Guest]

You may like to look at the Indirect function. Using this function you can
construct a reference to another worksheet using a formula. For instance if
you use the following formula

=Indirect(A1 & "!B1") then the formula will refer to Data 1's Cell B1 if the
Cell A1 contains Data 1 but will refer to Data 2's cell B1 if A1 contains the
value Data 2.

Alok Joshi