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...
Using cell value to define sheet reference, e.g.: =A1!B2
google spreadsheets
You can do this with the indirect formula:=ARRAYFORMULA(INDIRECT('data(&A1&)'!A:A))