How To Use Copilot To Make A Dynamic Search Bar In Excel

Download the excel file used in this tutorial 👇 https://drive.google.com/file/d/1x7gH... In this video, we'll make a dynamic search bar that will find records from an excel dataset in real time based on multiple criteria. This solution doesn’t require any filter formula and will work in all versions of excel. We will start by creating a search box and a drop-down control by using activeX controls from the developer option. Then, we'll use ‪@Microsoft‬ Copilot to give us the VBA code that will dynamically find and match the value of criteria with the column headers of the dataset. And then automatically filters the record in real time from multiple columns of the dataset as we type our search query. #excel #exceltutorial #copilot Microsoft Copilot prompts used in this tutorial: PROMPT 1 I want to make a dynamic search bar in Ms Excel that finds and filters records from the dataset in real time. I have a sheet named “Sheet1” that contains my dataset. The dataset is inside a table named “employeelist” Using the activeX controls under Developer tab, I have added the following controls on “Sheet1” Text box named “searchbox” To do: When I type search query into the “searchbox”, I want the matching records/rows to filter in real time from the “employeelist” table based on the “Full Name” (column header). Note: “Full Name” is a column header in “employeelist” table. Please dynamically find the “Full Name” (Column Header) from “employeelist” table so that the functionality remains intact even if the column position changes. If no data is found, show notification “no matching records found” Act as a professional excel VBA script writer and write me a complete working VBA code to achieve this functionality. PROMPT 2 Now I want to extend this search functionality by adding multiple criteria. Using the activeX controls under Developer tab, I have added the following additional control on “Sheet1” Combo box named “cmbcriteria” Step 1: Add the following options in the “cmbcriteria” combo box on “Sheet1” that initialize on ‘thisworkbook’ open event using ws.OLEObjects("cmbCriteria").Object.AddItem “Employee ID”, “Full Name”, “Gender”, “Department”, “Job Type”, “Work Type”, “Empl Type”, “State” Note: The above criteria options are same as the column headers in “employeelist” table. Therefore, please dynamically match the value of criteria with the column headers in the “employeelist” table so that the functionality remain intact even if the column position changes. Step 2: Now when I select the criteria from “cmbcriteria” combo box and enter value in “searchbox”; Get the selected criteria from “cmbcriteria” combo box Match the value of that criteria from the column headers of “employeelist” table Then find and filter matching records / rows based on the value being entered in “searchbox” If no data is found, show notification “no matching records found” Act as a professional excel VBA script writer and write me a complete working VBA code to achieve this additional functionality Chapters: 0:00 - Overview and demo 0:24 - Single Criteria Search Bar 5:00 - Multi Criteria Search Bar (Dynamic) 9:41 - Conclusion