Forum Discussion
Formula getting data from sheets with different names
Hello, I have a file with 12 different sheets (one for each month) that all have the same format. I’m creating a new sheet with graphics. To feed this graphics, I created a table (on this new sheet) that gets data from page Jan23, for example cells A5, B5 and C5. So the cell on the new page says: ‘Jan23’!A5. I also have a drop down list (on this new page) with the names of all the 12 pages (12 months). What I wanted to do is that the cell that has the formula (‘Jan23’!A5) could replace the name of the sheet by the one I select on the drop down list. Is this possible to do?
=INDIRECT(ADDRESS(5,COLUMN(),,,CONCATENATE($B$9,23)))You can try this formula which is in cell B5 and copied to the right. The data validation is in cell B9 and the reference list for data validation is in range A9:A20.
2 Replies
Here is a better solution:
You can get sheet names from one sheet to another with an easy formula...
on sheet1 have a formula in cell a1 that includes the sheet name reference: =Sheet1!B1
=TEXTBEFORE(TEXTAFTER(FORMULATEXT(sheet1!a1), "="), "!")
=INDIRECT(ADDRESS(5,COLUMN(),,,CONCATENATE($B$9,23)))You can try this formula which is in cell B5 and copied to the right. The data validation is in cell B9 and the reference list for data validation is in range A9:A20.