Forum Discussion

lowdencaft's avatar
lowdencaft
Occasional Reader
Jan 30, 2026

Excel sheets that populate a drop down menu

  1. Hello

    Is there a way to have a drop-down list of all the sheets in a workbook in a selected cell and the selected sheet from the list's data populates other cells in the first sheet?

    My goal : To easily see what machines are connected to a list of RJ45 wall points. In the workbook, there is 1 sheet that has a list of wall points . Each machine has its own sheet. Each machine sheet has lots of corresponding data that is not pertinent to the RJ45 wall point sheet.

    My goal is to select a cell next to a given wall point row in the RJ45 wall point sheet, that drops down a list of machines (that are all the machine sheets in the workbook). The selected dropdown list machine, will populate pre-defined adjacent cells with corresponding data  from the selected machine sheet.

     

    1.  

    2. Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from.
    3. Once a machine is selected, the hardware names and model columns that are on the same row are populated with the corresponding data on the machine sheet.
    4.  

    I have asked ChatGTP and Gemini AI to help ... without any success.

    Is this possible to do?

    Thank you

     

Excel for web

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jan 30, 2026

    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.

Resources