Creating complex search function

[geperi]

Hi,

I have the following data in a sheet, and I am trying to create an easy to use search box in Excel that can give me the output I need.

The reason I need a search function is that I have more than 50 people doing tasks, and the excel has more than 50 columns. A nice search function would make things much easier.

Input: the tasks (there are no duplicates)
Output 1: Date
Output 2: Person

Is there a nice way to build a search function in Excel, where I can input the name of the task (maybe from a drop-down) and then I get the output 1 and 2 to appear automatically on separate cells?

Thank you!

Person 1	Person 2	P3	P4	P5	Etc..
Date 1	Date 2	date 3	Date 4	Date 5
Task 1	Task A	AA	AB	CF
Task 2	Task B	BB	BA	CG
Task 3	Task C	CC	CA	GH
Task 4	D		DA	KL
Task 5	E		EA

<tbody>
</tbody>

 

[NickRed18]

The tasks you are looking up (Input), could you be looking up Task 1, Task 2, Task 3, Task 4, Task 5, Task A, Task B, Task C, etc.... just every task you listed? Or would you be looking up say Task 1, and then for the Person # you want it will output the first task for that person?

 

[JLGWhiz]

This is event code. It assumes your data matrix begins in row 3, column A. It also assumes cell A1 as the input cell and cells B1:B2 as the output cells. To use the code once the data has been configured as above, right click the sheet name tab, then click 'View Code' in the pop up menu. Copy and paste the code into the large code pane of the vb editor. Close the editor and save the workbook as a macro enabled workbook to preserve the code. The code should now run when you make an entry into range A1 of the sheet and should return data from rows 3 and 4 of the data matrix for the column where a match is found for the value in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Hndl:
Dim fn As Range, rng As Range, lr As Long, lc As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
        Set rng = .Range("A3", .Cells(lr, lc))
        Set fn = rng.Find(Range("A1").Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                .Range("B1") = .Cells(3, fn.Column).Value
                .Range("B2") = .Cells(4, fn.Column).Value
            End If
    End With
End If
Hndl:
Application.EnableEvents = True
If Err.Number <> 0 Then
    MsgBox Err.Number & ":  " & Err.Description
    On Error GoTo 0
End If
End Sub

 

[63falcondude]

Here's one option:

Excel 2010
	A	B	C	D	E	F	G	H
1	Person 1	Person 2	Person 3	Person 4	Person 5		Task (Input)	Task BB
2	Date 1	Date 2	Date 3	Date 4	Date 5		Date (Output)	Date 3
3	Task 1	Task A	Task AA	Task AB	Task CF		Person (Output)	Person 3
4	Task 2	Task B	Task BB	Task BA	Task CG
5	Task 3	Task C	Task CC	Task CA	Task GH
6	Task 4	Task D		Task DA	Task KL
7	Task 5	Task E		Task EA
Sheet1

Cell Formulas
Range	Formula
H2	{=INDEX(A2:E2,SUMPRODUCT(MAX((A3:E7=H1)*(COLUMN(A3:E7))))-COLUMN(A3)+1)}
H3	{=INDEX(A1:E1,SUMPRODUCT(MAX((A3:E7=H1)*(COLUMN(A3:E7))))-COLUMN(A3)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[geperi]

This is event code. It assumes your data matrix begins in row 3, column A. It also assumes cell A1 as the input cell and cells B1:B2 as the output cells. To use the code once the data has been configured as above, right click the sheet name tab, then click 'View Code' in the pop up menu. Copy and paste the code into the large code pane of the vb editor. Close the editor and save the workbook as a macro enabled workbook to preserve the code. The code should now run when you make an entry into range A1 of the sheet and should return data from rows 3 and 4 of the data matrix for the column where a match is found for the value in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Hndl:
Dim fn As Range, rng As Range, lr As Long, lc As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
        Set rng = .Range("A3", .Cells(lr, lc))
        Set fn = rng.Find(Range("A1").Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                .Range("B1") = .Cells(3, fn.Column).Value
                .Range("B2") = .Cells(4, fn.Column).Value
            End If
    End With
End If
Hndl:
Application.EnableEvents = True
If Err.Number <> 0 Then
    MsgBox Err.Number & ":  " & Err.Description
    On Error GoTo 0
End If
End Sub

Hi,

I used the exact code and set up the data in the page starting with A3, and tried to copy paste one task in A1 but nothing at all showed up in B1:B2..

 

[geperi]

Hi all,

I thought about this a bit more, and I think it makes most sense if I go for a drop down functionality. Do you think this is do-able?

Input: Drop down 1: List of all dates
Input: Drop down 2: Based on the date chosen it will only show the Tasks associated with that specific date
Output: Text box: Person responsible

 

[geperi]

Hi all,

I thought about this a bit more, and I think it makes most sense if I go for a drop down functionality. Do you think this is do-able?

Input: Drop down 1: List of all dates
Input: Drop down 2: Based on the date chosen it will only show the Tasks associated with that specific date
Output: Text box: Person responsible

Hi friends,

Is anyone able to help with a solution for my previous set-up above?

Would greatly appreciate it!