Forum Discussion
Excel sheets that populate a drop down menu
The answer is yes, it is possible. The problem is that the way you have it set up makes it challenging. If instead of each machine having a different tab, you had 1 master table with a column that defined the machine name then you just use a FILTER() or a XLOOKUP() to find the data you want. Having data across multiple tabs makes this more challenging but there are still 2 (or more) approaches:
a) you can use INDIRECT() and build the sheet reference inside the INDIRECT(). There are a number of drawbacks to using INDIRECT include it being volatile (meaning it always gets updated which can cause performance issue if there are a lot of them or they reference a lot of data) and they don't necessarily automatically update the reference location if the sheet structure changes.
b) if you have a list of all the sheet names (i.e. machine names) in order then you can use HSTACK or VSTACK to the 3-d reference (i.e. the range of data on all the sheets and across all sheets so like: 'Machine1:Machine100'!A1:Z1000) and then you can CHOOSECOLS (or rows) based on which machine name and how many columns per sheet. and then filter/lookup further after that.
so conceptually those are some ways to tackle this problem. w/r to b) I like to have a cell like A1 on each sheet be dedicated for that sheet's name and then add dummy 'start' and 'end' sheets to make the process easier (i.e. you can then dynamically get the list of sheet names).
but without a sample workbook and more information it is hard to help too much more than that.