Using cell value to define sheet reference, e.g.: "=A1!B2"

Question

I have data across different sheets in my Google Spreadsheet, called "data(Joe)", "data(John)", etc. From another spreadsheet I want to review that data in a structured way. I would like to be able to switch between the datasets by entering the name "Joe" or "John" into a cell and then the formula knowing to look into the appropriate sheet.

For example, if in my sheet I have the formula =ARRAYFORMULA('data(Joe)'!A:A) that pulls across the A column data from another sheet. How can I implement this so that the name Joe is referenced by a cell, e.g.: =ARRAYFORMULA('data('A1!A:A) with cell A1 containing the word Joe?

Hope it's possible, and easy, but I suspect not...

Answer 1

You can do this with the indirect formula:

=ARRAYFORMULA(INDIRECT("'data("&A1&")'!A:A"))