Help

G

GaryHJones

New Member
Joined
Sep 28, 2019
Messages
2
I have been trying to find a solution to my problem, but cant seem to find one.

i have a spreadsheet with names in column A, and events in column B and dates in column C.

the format of the sheet, is like this

Column A Column B Column C
[TABLE="width: 361"]
<colgroup><col width="95" style="width: 71pt;"><col width="173" style="width: 130pt;"><col width="93" style="width: 70pt;"></colgroup><tbody>[TR]
[TD="class: xl67"]Fred Bloggs[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Freestyle[/TD]
[TD="class: xl68, align: center"]09/03/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Freestyle[/TD]
[TD="class: xl68, align: center"]31/03/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 200 Freestyle[/TD]
[TD="class: xl68, align: center"]01/02/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 400 Freestyle[/TD]
[TD="class: xl68, align: center"]21/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 800 Freestyle[/TD]
[TD="class: xl68, align: center"]13/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Breaststroke[/TD]
[TD="class: xl68, align: center"]22/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Breaststroke[/TD]
[TD="class: xl68, align: center"]01/02/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 200 Breaststroke[/TD]
[TD="class: xl68, align: center"]02/02/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Butterfly[/TD]
[TD="class: xl68, align: center"]21/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Butterfly[/TD]
[TD="class: xl68, align: center"]07/07/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 200 Butterfly[/TD]
[TD="class: xl68, align: center"]03/02/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Backstroke[/TD]
[TD="class: xl68, align: center"]22/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Backstroke[/TD]
[TD="class: xl68, align: center"]22/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 200 Backstroke[/TD]
[TD="class: xl68, align: center"]21/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Individual Medley[/TD]
[TD="class: xl68, align: center"]09/03/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 200 Individual Medley[/TD]
[TD="class: xl68, align: center"]22/09/2019[/TD]
[/TR]
[TR]
[TD="class: xl67"]Bill Bob[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Freestyle[/TD]
[TD="class: xl68, align: center"]16/06/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Freestyle[/TD]
[TD="class: xl68, align: center"]23/03/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Breaststroke[/TD]
[TD="class: xl68, align: center"]16/06/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Butterfly[/TD]
[TD="class: xl68, align: center"]16/06/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 50 Backstroke[/TD]
[TD="class: xl68, align: center"]16/06/2019[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67"]Male 100 Individual Medley[/TD]
[TD="class: xl68, align: center"]16/06/2019[/TD]
[/TR]
</tbody>[/TABLE]

i have a another sheet where i want to be able to select a name from a validated List of Names , and return the correct dates for each of the events listed.

i don't want to have to copy the names down, against the events as there are 100s of names and events.
thanks for any help anyone can give.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
This assumes you are using a validation drop down list. You did not specify where on "another sheet" you have the list but the code below uses cell A1 of Sheet 2 to illustate the method. You can change the code to reflect the correct cell for the drop down list.
Code: 
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, rng As Range, cel As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If sh2.Range("A1") <> "" Then
        Set fn = sh1.Range("A:A").Find(sh2.Range("A1").Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If fn.End(xlDown).Row > lr Then
                    Set cel = sh1.Cells(lr, 3)
                Else
                    Set cel = fn.End(xlDown).Offset(-1, 2)
                End If
                Set rng = sh1.Range(fn.Offset(2, 1), cel)
            End If
        rng.Copy sh2.Range("B3")
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
233
Fluff
Fluff
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
365
Cubist
Cubist
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
235
rollis13
rollis13
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
H
  • Solved
Change gender and modify some words accordingly.
Replies
5
Views
862
harzer
H

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top