Forum Discussion
Excel sheets that populate a drop down menu
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.
- Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from.
- 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.
I have asked ChatGTP and Gemini AI to help ... without any success.
Is this possible to do?
Thank you
3 Replies
Hello lowdencaft,
Yes, this is possible by combining Data Validation, INDIRECT, and VBA.
Step 1: Dropdown - Select your MACHINE NAME cells, go to Data → Data Validation → List, and either type sheet names or point to a range with the names.
Step 2: Pull data with INDIRECT - Assuming the dropdown is in I2 and hardware is in J2 on the machine sheet, use =IFERROR(INDIRECT("'" & $I2 & "'!J2"), "") and repeat for other fields by changing the cell reference.
Step 3: VBA for automatic dropdown and auto-fill- Sub PopulateMachineDropdown() Dim ws As Worksheet, mainWS As Worksheet, sheetNames As String Set mainWS = ThisWorkbook.Sheets("RJ45") mainWS.Columns("B").Validation.Delete For Each ws In ThisWorkbook.Sheets If ws.Name <> mainWS.Name Then sheetNames = sheetNames & ws.Name & "," Next ws sheetNames = Left(sheetNames, Len(sheetNames) - 1) With mainWS.Range("B2:B100").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sheetNames .IgnoreBlank = True: .InCellDropdown = True End With End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim ws
As Worksheet, selectedSheet As String If Not Intersect(Target, Me.Columns("B")) Is Nothing Then Application.EnableEvents = False selectedSheet = Target.Value On Error Resume Next Set ws = ThisWorkbook.Sheets(selectedSheet) On Error GoTo 0 If Not ws Is Nothing Then Target.Offset(0, 1).Value = ws.Range("J2").Value Target.Offset(0, 2).Value = ws.Range("K2").Value Else Target.Offset(0, 1).Resize(1, 2).ClearContents End If Application.EnableEvents = True End If End Sub Result: Dropdown auto-lists all machine sheets, and selecting a machine fills adjacent fields automatically.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.
Without seeing your workbook, it's hard to be certain about this, but the way you describe it leads me to underscore what m_tarler says in his first paragraph: you should seriously consider redesigning your workbook so that it takes the form of a single large database. Then you can take advantage of Excel's many tools to parse, filter, select from that single database. As it is, you've slightly computerized what would have been the sensible approach back in the days of each machine being tracked on its own large paper sheet. It would be better if you rethink how you approach it, looking for a way to take more full advantage of Excel.
