Copying data from specific sheet if a value exists

first5.0

New Member
Joined
Jan 16, 2006
Messages
44
Hey guys. Not sure what to search for on this.

I have a Sheet (sheet1) which lists a name (column a) and their join date (column b).

I'd like to have the ability for someone to enter a date on a seperate sheet (sheet2) and receive a list of names who's join date is the same as the date they entered. The list of names should be displayed on sheet2.

Is there a built in function for this or would I need to create a macro?

Thanks.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi -
right click sheet2 > view code then paste this code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Target.Value = "" Then Exit Sub
Range("a2:a" & Rows.Count).ClearContents
With Sheets("Sheet1").Columns("b")
    Set c = .Find(Sheets("Sheet2").Range("a1").Value, , , xlWhole)
        If Not c Is Nothing Then
            f = c.Address
            Do
                Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Offset(1) = c.Offset(, -1).Value
            Set c = .FindNext(c)
            Loop Until f = c.Address
                Else
                    MsgBox "Date not found!", vbInformation + vbOKOnly, "No records to display"
        End If
End With
End Sub
from sheet2 A1, type a date. the list of name will show in sheet2 A2 down.
hope it helps.
 

first5.0

New Member
Joined
Jan 16, 2006
Messages
44
PERFECT. Works like a charm.

Took me a little bit to adapt it to my real scenario (my columns and sheets aren't as straight forward as my original question stated). But it works great.

I like the fact that you added in a message stating that it did not find the date if the date was not present. Very user-friendly.

Thanks!!!!!
 

Forum statistics

Threads
1,136,709
Messages
5,677,324
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top