Sounds like that would be a painfully long formula that is prone to break (and obviously you would want to be able to add sheets etc.
Maybe if i understand you correctly this in vba
Function datein(range1 As Range)
Dim myrange As Range
Dim dateinf As String
On Error Resume Next
For Each sh In ActiveWorkbook.Worksheets
Set myrange = sh.Cells.Find(What:=Format(range1, "mmmm dd, yyyy"), After:=[a1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
aa = myrange.Value
If aa <> "" Then
datein = myrange.Offset(1, 0).Value
For i = 2 To 10
datein = datein & " " & Chr(10) & myrange.Offset(i, 0).Value
Next
Exit For
Else: End If
Next sh
End Function
then use =datein(B1)
Bookmarks