Rename text in a range of formulas

[wilsnachjh]

Good Afternoon, I need to change a range of text, in a formula, in a range of columns. At the moment I am replacing the text one by one. Eg. I have 65 worksheets in a workbook and each sheet are names differently. Then I have a comparison sheet where are the data are combined on. ='Eastern Cape - Grahamstown'!$E15 I need to replace the name Grahamstown in the formula with other worksheet names.

Is there an easier solution to this instead of me putting in the formula manually.

Thanks

Jonathan

[excelliot]

Hi you can use Indirect function, wherein you can put sheet name in one cell & by using indirect formula you can refer particular cells of that sheet.


Cheers!!

[ragulduy]

Use the find and replace tool.

[TheCman81]

Does it always have Eastern Cape - at the start of the sheet name?

You can use something like the below (A2 is the sheet name ie Grahamstown

=INDIRECT("'Eastern Cape - "&A2&"'!"&"E15")