Search and return based on dropdown

Microsoft Excel 2003 (full product)
January 20, 2010 at 08:45:29
Specs: Windows XP
Hi all,
Heres what I want to do. I have a data validation dropdown in cell d3, and the value of d3 (whichever I select) I want a search to be performed based on the value of that cell going through every sheet, and returning the value of cell d5 on every sheet to e3 on the sheet called Report1

See More: Search and return based on dropdown

Report •

January 20, 2010 at 09:58:10
re: returning the value of cell d5 on every sheet to e3 on the sheet called Report1

You want to return the value of multiple cells ("cell d5 on every sheet") to a single cell?

Report •

January 21, 2010 at 00:52:01
My bad, I just meant the the 'E' column
thanks again

Report •

January 21, 2010 at 05:54:33

You say that you want the value of D5 on every sheet returned to cells in column E.

If that is what you want, then all you need is a series of formulas in the form = 'sheetX'!D5
... but this will not change based on the value in cell Report1 D5

You could lookup the value selected in the drop-down in D3, in a range of cells on each sheet and return an adjacent value.

For example:
The drop-down in cell D3 on a worksheet named Report1 has letters A to E.
There are 4 worksheets named Sheet2 through Sheet5

On each of these sheets is a table in the range D5 to E9
Column D cells contain the letters A to E
Column E contains values to be returned

	D	E
5	A	2-D5
6	B	2-D6
7	C	2-D7
8	D	2-D8
9	E	2-D9

Sheet 3 has values in column E 3-D5 etc
Sheet 4 has values in column E 4-D5 etc etc.

Back on the Report1 worksheet you have a lookup formula for each of the worksheets Sheets 2 to 5
In cell E5 you have

and the same formula in cells E4, E5 and E6, but changed for the different worksheets. E8 is:

When you select E from the drop down you return the value from each worksheet adjacent to the letter E:

	D	E
2	Select	
3	E	
4	Sheet	Value
5	Sheet 2	2-D9
6	Sheet 3	3-D9
7	Sheet 4	4-D9
8	Sheet 5	5-D9

Is this the sort of thing you were trying to do?


Report •

Related Solutions

January 21, 2010 at 06:14:07
Right click the sheet tab for Report1, choose View Code and paste this code in the pane that opens.

The code assumes that you do not want to check the sheet named Report1. If you do, then some modifications will need to be made.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow, nxtSht, c, firstAddress, notFound
'Determine if change was to dropdown cell
  If Target.Address = "$D$3" Then
'Calculate Last Row of data in Column E
   nxtRow = Range("E" & Rows.Count).End(xlUp).Row
'Disable events so this code doesn't run while
'values are placed in Column E
    Application.EnableEvents = False
'Loop through sheets searching for Target Value
     For nxtSht = 1 To Sheets.Count
'Don't check Report1
      If Sheets(nxtSht).Name <> "Report1" Then
       With Sheets(nxtSht).Cells
        Set c = .Find(Target, LookIn:=xlValues, lookat:=xlWhole)
'Copy value to Column E and keep searching same sheet
         If Not c Is Nothing Then
          firstAddress = c.Address
             nxtRow = nxtRow + 1
             Range("E" & nxtRow) = c
             Set c = .FindNext(c)
           Loop While Not c Is Nothing And c.Address <> firstAddress
'Increment Not Found Counter
          Else: notFound = notFound + 1
         End If
       End With
      End If
  End If
'If Target value was never found, inform the user
      If notFound = Sheets.Count - 1 Then _
        MsgBox Target.Value & " Was Not found On Any Sheet"
'Enable events so code will run next time
    Application.EnableEvents = True
End Sub

Report •

January 21, 2010 at 06:24:17
Now that I've reread your post again, I'm still confused.

You say the drop down is in D3 and you want to search all sheets for the value in D3. How does that relate to pulling the values from D5 on every sheet into Column E?

The code I offered above will search for the value from the Report1 dropdown on every other sheet and put it in Column E, regardless of what cell it is in on those sheets. In other words, if Report1!D3 contains 25, and 25 is found 3 times on the other sheets, Column E will show:


Change the drop down to 80, and assume 80 is found twice on the other sheets, Column E would now show:


Is that what you want?

Report •

January 21, 2010 at 06:50:51
I appreciate your help, I probably couldve explained it better. Bascially, d5 contains a persons name, and the other cell contains the name of a group. So when i use the dropdown it has the name of the groups, when i select a value, i want t osearch through every sheet for that value, and if it is found, return the persons name.
thanks again

Report •

Ask Question