How to find a list of names in another sheets

edit

Hello (again)

here the (promised) macro function. Just put it into Tools -> Macros -> "Edit Macros" and you can call it from inside any cell via

=FRM("A1:B1")

• The 1st argument is the range to be searched for quoted as a string. The range can span multiple rows and columns.

One limitation i have to meantion. The area that is searched on each sheet is "only" A1:Z100 If that is not sufficient you can easily increase it or make it a second argument to the function.

Proof of work:

Here the macro function you have to import:

REM (F)ind(R)ange(M)atches
Function FRM(range as string)
    Dim out as string
    Dim start as string
    Dim match as boolean 
    Set oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
    Const maxSheetSearchRange = "A1:Z100"
    Set oASheet = ThisComponent.CurrentController.ActiveSheet  
    Set oMRange = oASheet.getCellRangeByName(range)
    start =  oMRange.getCellByPosition( 0, 0 ).String
    Set oSheets = ThisComponent.getSheets()
    For k = 0 To oSheets.getCount() - 1
        Set oSheet = oSheets(k)  
        Set oRange = oSheet.getCellRangeByName(maxSheetSearchRange)
        For i = 0 To oRange.Rows.getCount() - 1             
            For j = 0 To oRange.Columns.getCount() - 1
                Set oCell = oRange.getCellByPosition( j, i )
                if ( oCell.String = start ) then
                    match = True 
                    For l = 0 To oMRange.Rows.getCount() - 1             
                        For m = 0 To oMRange.Columns.getCount() - 1
                            Set oMCell = oMRange.getCellByPosition( m, l )
                            if ( not (l = 0 and m = 0) ) then
                                Set oCell2 = oRange.getCellByPosition( j+m, i+l )
                                if ( oCell2.String <> oMCell.String ) then
                                    match = False
                                    Exit For
                                endif
                            endif 
                        Next
                        if (match = False) then
                            Exit For
                        endif
                    Next 
                    if (match = True) then
                        oConv.Address = oCell.getCellAddress()
                        out = out & "Match: " & oConv.PersistentRepresentation & chr(10) 
                    endif
                endif 
            Next 
        Next 
    Next 
    FRM=out
End Function

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let's (continue to) "Be excellent to each other!"