CODEWITHSUNDEEP
excel
lou
lou

Reputation: 21

Changing sheet names using a formula

I have an Excel workbook with various tabs on it.

In Sheet 1 (named: ‘Pricing Calculator’) you can input various data and it calculates a price.

Sheet 2 (named: ‘Final Copy’) is basically the same as Sheet 1, however it is not used for inputting data. So in this sheet cell A1=’Pricing Calculator’!A1 etc. for most of the sheet.

However, sometimes Sheet 1 (‘Pricing Calculator’) is copied and that and the copied sheets renamed to ‘Option 1’, ‘Option 2’, ‘Option 3’ etc. Sheet 2 (‘Final Copy’) only needs to use information out of one of these sheets, but which one will depend on which option is chosen.

I have tried adding a cell into Sheet 2 (say it was A30) where you can type in exactly the name of the sheet to get the data from, and changing the cells to =’A30’!A1 etc, but this doesn’t work because it is looking for a sheet called ‘A30’.

It works by clicking ‘replace’ and changing everything that says ‘Pricing Calculator’ to ‘Option 2’ or whatever. But I have got to password protect all of the sheets so that no one can change them, therefore I can’t use the replace method!

Does anyone have any ideas as to what I could do? I would prefer to avoid using macros or VBA if possible.

Upvotes: 0

Views: 1012

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You could use INDIRECT() for this. 

=INDIRECT("'" & A30 & "'!A1")

Upvotes: 1

Related Questions