search based on a combobox selection

February 18, 2010 at 07:54:34
Specs: Windows XP
Hi all,
I have a dropdown list with over 1500 names in it. Each of the people works in a department. Each of these departments has a worksheet named after the department. I want to be able to put all of these names in a combobox, and when I select their name from the combobox, I want excel to perform a search throughout all the workbook and highlight the row with the selected name in it, and highlight it. Can it be done?

See More: search based on a combobox selection

Report •

February 18, 2010 at 09:52:20

Just to confirm,

From a combobox, you select an employee name.

Each employee name appears in their department's worksheet.

Once you have selected the employee name from the combobox, you need a way of finding and highlighting that employee's row in their department's worksheet.

Please confirm if I have this right - or else correct / clarify further


Report •

February 18, 2010 at 12:41:47
Humar thats exactly it

Report •

February 18, 2010 at 15:36:38

I was afraid you were going to say that !

I will see if I can come up with something - but not this evening !


Report •

Related Solutions

February 19, 2010 at 07:03:42

I suggest the following approach which presents a member of staff's record to the user when their name is selected from a combobox.

Add a new worksheet which is linked to each departments worksheet.
The columns in this 'All Depts' worksheet are the same as the columns used in the departmental worksheets.

For this example I had departments named Dept_A, Dept_B and Dept_C

Go to each departmental worksheet and add a column to the right of the name and enter the following formula in cell B2 (row 1 has headings, column A is names.
=A2 & " " & "Dept_A" (the department's name), drag down all used rows and a few extra rows to allow for new staff, and repeat for each departmental worksheet. Column B is now a unique name - department combination.

Then copy all the used cells and include a number of empty rows below the last used row to allow for new staff in each department.
Paste Special, Paste-link to the 'All Depts' worksheet.

In the next empty column in the All Depts worksheet create a column labeled 'Sort'
If this is column G enter this in G2 =IF(A2=0, "ZZ",A2) assuming that column A contains the staff names.

Now Sort the 'All Depts' worksheet based on the Sort column.

The spare rows will sort to the bottom.

Now 'name' the second column of data (name and department combined)
Select the cells in column B, excluding the header row, but including the rows with zeros (so that new staff will be included later). Goto Insert - Name - Define. In the dialog box name the range 'StaffList' (no spaces allowed) and click Add, then OK.

On your main worksheet with the drop downs, use the named range 'StaffList' as the linked list for the ComboBox.
In design mode, right-click Combobox, select Properties
ListFillRange enter StaffList
LinkedCell enter D5 (or another cell - it's not critical)

Close properties box and exit design mode
The drop down will now show names and departments - so allowing correct selection of staff with same or similar names in different departments.

The staff name will appear in cell D5

To display all the staff members details enter this in cell E5
=VLOOKUP($D$5,'All Depts'!$B$2:$E$88,2,FALSE)
Adjust the lookup range in column B as required.
Drag this to the right and change the column offset numbers.

As a name/department is selected, the record for that individual will now show in cells D5, E5 etc.

This doesn't actually take you to the original record, but it does present the record to the user, which I trust is a satisfactory alternative. If not, let me know. To go to the actual record on a departmental worksheet will require a macro.


Report •

February 21, 2010 at 09:15:02
I have another, far more complex difficulty that I wanted to get your opinion on. I have 3 sheets each containing thousands of records in total. I am creating one file to count how many people from each group (there are up to 50) are working in a certain department and section (there are up to 500). I had tried the option of creating =COUNTIFs for every record, and this did work, but on the scale I need it for it would be useless. I had the idea of creating three dropdowns on this counter workbook. One for the group, one for the dept and one for the section, so that when i select the relevant group, section and dept, vb code will count the amount of people from that group in the selected dept and section. Its a massive thing, just thought id put it out there to see if anyone had any ideas. thanks!

Report •

Ask Question