Code to bring up info if name is entered on a separate sheet

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102
I want a code to run some type of a lookup which would bring up all the information, specific cells within the row of the name I type in to a sheet named "Search".<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Looking in several sheets named "??? Register" (the 3 question marks will be different letters or numbers on each Register sheet) starting in row 137.<o:p></o:p>
<o:p></o:p>
If the name does exist I want the information from each time the name is found, from columns B, C, D, E, G, H, & I to be listed on the Search sheet.<o:p></o:p>
If the name doesn't exist on the ??? Register sheets I would like a pop up to open and indicates "Name not found" or "Name doesn't exist".<o:p></o:p>
<o:p></o:p>
Is this possible??<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>:confused:<o:p></o:p>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Have you looked at AdvanceFilter or even MSQuery? Both are much more suited for this!!

lenze
 

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102
Thanks for your quick response lenze!:)

I did try MSQuery but because I know so little about it I couldn't get it to do want I wanted.

I would like to use a code so that it will be very easy for others in our office to use the Search page.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Did you mean to exclude Column "F" from being brought over??

lenze
 

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102

ADVERTISEMENT

Yes, I believe so. Column F is the column that contains the names that will be searched for from the Search sheet.

I have no control over what is in the columns on the ??? Register sheets so I must try to work with it.;)
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
A few more questions!!
1) Will the name be in more that one sheet??
2) If so, do you need to identify the sheet with the data?
3) How many register sheets are there?

lenze
 

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102

ADVERTISEMENT

Yes, the name will possibly be in more than one sheet.

Yes, I think bringing up the sheetname of the name being searched for would be a benefit.;)

The number of register sheets may vary from 10 to 20 but for the most part there will be 12 Register sheets.:)
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
This should get you started
Assumptions
1) The Name is entered in $B$2 on the "Search" sheet
2) On the "Search" sheet, A5:H5 contain Headers (H5 is the "Source Sheet")
3) The name only appears once in each sheet (this can be changed if needed)
4) You don't want to retain previous retrievals
5) You don't need to retrieve the Name (Column "F" on source sheet)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Dim ws As Worksheet
Dim sh As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim C As Range
Dim D As Range
Set sh = Sheets("Search")
Dim Flag As Boolean
Flag = False
Set D = sh.Range("$A$6:$H" & Cells(Rows.Count, "H").End(xlUp).Row + 1)
D.ClearContents
For Each ws In Worksheets
If ws.Name <> "Search" Then
  LR1 = ws.Cells(Rows.Count, "F").End(xlUp).Row
  With ws.Range("$F$2:$F" & LR1)
    Set C = .Find(Target, LookIn:=xlValues)
    If Not C Is Nothing Then
       LR2 = sh.Cells(Rows.Count, "A").End(xlUp).Row
       ws.Cells(C.Row, 2).Resize(1, 4).Copy sh.Cells(LR2 + 1, "A")
       ws.Cells(C.Row, 7).Resize(1, 3).Copy sh.Cells(LR2 + 1, 5)
       sh.Cells(LR2 + 1, 8) = ws.Name
       Flag = True
    End If
  End With
End If
Next ws
If Flag = False Then MsgBox "Name Not Found"
End Sub
HTH
lenze
 

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102
Thank you for the code lenze.:cool:

Is there a way to bring the information up on the Search sheet if the name appears more than once on a register sheet? ;) We may have the name in the Register 3 or 4 times.

I tried altering the code with no luck. This is the first time I've seen these particular pieces of code and I do not know what they are doing.:(
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
I anticipated that :biggrin:. We can add a FindNext
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Dim ws As Worksheet
Dim sh As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim C As Range
Dim D As Range
Set sh = Sheets("Search")
Dim Flag As Boolean
Flag = False
Set D = sh.Range("$A$6:$H" & Cells(Rows.Count, "H").End(xlUp).Row + 1)
D.ClearContents
For Each ws In Worksheets
If ws.Name <> "Search" Then
  LR1 = ws.Cells(Rows.Count, "F").End(xlUp).Row
  With ws.Range("$F$2:$F" & LR1)
    Set C = .Find(Target, LookIn:=xlValues)
    If Not C Is Nothing Then
       fstAdd = C.Address
       Do
       LR2 = sh.Cells(Rows.Count, "A").End(xlUp).Row
       ws.Cells(C.Row, 2).Resize(1, 4).Copy sh.Cells(LR2 + 1, "A")
       ws.Cells(C.Row, 7).Resize(1, 3).Copy sh.Cells(LR2 + 1, 5)
       sh.Cells(LR2 + 1, 8) = ws.Name
       Set C = .FindNext(C)
       Loop While Not C Is Nothing And fstAdd <> C.Address
       Flag = True
    End If
 End With
End If
Next ws
If Flag = False Then MsgBox "Name Not Found"
End Sub
HTH
lenze
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,946
Messages
5,514,327
Members
408,997
Latest member
VJarvis

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top