Nested VLOOKUP I think

[CordingBags]

I need to find on which sheet within my workbook a target value appears.

The target value is a cell that contains the sum of two other cells, one a date and one a time. Target values are held in column BB of each sheet

I need to know if this "number" is unique within my workbook
Or if there are duplicates, which sheet(s) they are on.
Ideally a list of sheets but an indication that a duplicate exists somewhere would suffice.

So far in cell BA5 I have =IF(VLOOKUP(BB5,'MENS EVE LGE 2'!BB5:BB318,1,FALSE)>1,"MENS EVE LGE 2",""), which is fine for the first sheet but there are 16 others to check and I cannot find the syntax to check the next sheet(s). This formula is copied down column BA to row 318 =IF(VLOOKUP(BB318,'MENS EVE LGE 2'!BB5:BB318,1,FALSE)>1,"MENS EVE LGE 2","")
Not even 100% sure I am using the correct function and hope someone can advise.

Basic logic, are the value of cells in column BB on current sheet unique or do any repeat on any other sheet within the workbook.
The value will only appear in cells BB5:BB318 of each worksheet.
Perhaps ignoring this range is "simpler" Does any other sheet contain the value held in Sheet ?? cell BB?
Or even is value in Cell BB? unique in this workbook YES/NO

Every worksheet will replicate the same calculation in column BA indicating the other half of the duplication should it exist.

If there is no duplication then #NA or similar e.g. "Not Found" should be returned.

Appreciate any help

Thanks
Paul

EXCEL 2016 Win 11

 

[HighAndWilder]

I need to find on which sheet within my workbook a target value appears.

The target value is a cell that contains the sum of two other cells, one a date and one a time. Target values are held in column BB of each sheet

I need to know if this "number" is unique within my workbook
Or if there are duplicates, which sheet(s) they are on.
Ideally a list of sheets but an indication that a duplicate exists somewhere would suffice.

So far in cell BA5 I have =IF(VLOOKUP(BB5,'MENS EVE LGE 2'!BB5:BB318,1,FALSE)>1,"MENS EVE LGE 2",""), which is fine for the first sheet but there are 16 others to check and I cannot find the syntax to check the next sheet(s). This formula is copied down column BA to row 318 =IF(VLOOKUP(BB318,'MENS EVE LGE 2'!BB5:BB318,1,FALSE)>1,"MENS EVE LGE 2","")
Not even 100% sure I am using the correct function and hope someone can advise.

Basic logic, are the value of cells in column BB on current sheet unique or do any repeat on any other sheet within the workbook.
The value will only appear in cells BB5:BB318 of each worksheet.
Perhaps ignoring this range is "simpler" Does any other sheet contain the value held in Sheet ?? cell BB?
Or even is value in Cell BB? unique in this workbook YES/NO

Every worksheet will replicate the same calculation in column BA indicating the other half of the duplication should it exist.

If there is no duplication then #NA or similar e.g. "Not Found" should be returned.

Appreciate any help

Thanks
Paul

EXCEL 2016 Win 11

Try this in a copy of your workbook.

A sheet named 'Duplicates' is created where the value is found.

There are two changes to be made in subFindInSheets.
(At the start)

Run subFindInSheets.

Credit to Pearson Software Consulting for the FindAll function, I just wrote the loop.
FindAll VBA Function

Public Sub subFindInSheets()
Dim rng As Range
Dim rngFound As Range
Dim Ws As Worksheet
Dim WsActive As Worksheet
Dim WsDestination As Worksheet
Dim s As String
Dim intRow As Integer
Dim intSheets As Integer
Dim intCells As Integer
Dim varValue As Variant
Dim blnFound As Boolean

  ActiveWorkbook.Save
  
  ' CHANGE THIS VALUE TO THE VALUE TO BE SEARCHED FOR.
  varValue = "1"

  ' CHANGE THIS SHEET NAME TO THE ONE TO BE EXCLUDED FROM THE SEARCH.
  Sheets("FirstSheet").Activate

  Set WsActive = ActiveSheet
  
  Application.DisplayAlerts = False
  On Error Resume Next
  Worksheets("Duplicates").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  
  Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Duplicates"

  Worksheets("Duplicates").Range("A1:B1").Value = Array("Sheet", "Cell")

  intRow = 2

  For Each Ws In Worksheets
      
    If Ws.Name <> WsActive.Name And Ws.Name <> "Duplicates" Then
  
      Set rngFound = FindAll(Ws.Range("A1").CurrentRegion, varValue, xlValues, xlWhole, xlByColumns, True, , , vbTextCompare)
        
      If Not rngFound Is Nothing Then
      
        blnFound = True
      
        intSheets = intSheets + 1
    
        For Each rng In rngFound.Cells
          Worksheets("Duplicates").Cells(intRow, 1).Resize(1, 2) = Array(Ws.Name, rng.Address)
          intRow = intRow + 1
          intCells = intCells + 1
        Next rng
          
      End If

    End If

  Next Ws
  
  If blnFound Then
    MsgBox "The value of " & varValue & " has been found " & intCells & " times  in " & intSheets & " worksheets.", _
      vbOKOnly, "Confirmation"
  Else
    MsgBox "The value of '" & varValue & "' has NOT been found.", vbOKOnly, "Whoops!"
  End If
  
End Sub

Public Function FindAll(SearchRange As Range, _
                FindWhat As Variant, _
               Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False, _
                Optional BeginsWith As String = vbNullString, _
                Optional EndsWith As String = vbNullString, _
                Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FindAll
' This searches the range specified by SearchRange and returns a Range object
' that contains all the cells in which FindWhat was found. The search parameters to
' this function have the same meaning and effect as they do with the
' Range.Find method. If the value was not found, the function return Nothing. If
' BeginsWith is not an empty string, only those cells that begin with BeginWith
' are included in the result. If EndsWith is not an empty string, only those cells
' that end with EndsWith are included in the result. Note that if a cell contains
' a single word that matches either BeginsWith or EndsWith, it is included in the
' result.  If BeginsWith or EndsWith is not an empty string, the LookAt parameter
' is automatically changed to xlPart. The tests for BeginsWith and EndsWith may be
' case-sensitive by setting BeginEndCompare to vbBinaryCompare. For case-insensitive
' comparisons, set BeginEndCompare to vbTextCompare. If this parameter is omitted,
' it defaults to vbTextCompare. The comparisons for BeginsWith and EndsWith are
' in an OR relationship. That is, if both BeginsWith and EndsWith are provided,
' a match if found if the text begins with BeginsWith OR the text ends with EndsWith.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range
Dim XLookAt As XlLookAt
Dim Include As Boolean
Dim CompMode As VbCompareMethod
Dim Area As Range
Dim MaxRow As Long
Dim MaxCol As Long
Dim BeginB As Boolean
Dim EndB As Boolean


CompMode = BeginEndCompare
If BeginsWith <> vbNullString Or EndsWith <> vbNullString Then
    XLookAt = xlPart
Else
    XLookAt = LookAt
End If

' this loop in Areas is to find the last cell
' of all the areas. That is, the cell whose row
' and column are greater than or equal to any cell
' in any Area.
For Each Area In SearchRange.Areas
    With Area
        If .Cells(.Cells.Count).Row > MaxRow Then
            MaxRow = .Cells(.Cells.Count).Row
        End If
        If .Cells(.Cells.Count).Column > MaxCol Then
            MaxCol = .Cells(.Cells.Count).Column
        End If
    End With
Next Area
Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)


'On Error Resume Next
On Error GoTo 0
Set FoundCell = SearchRange.Find(what:=FindWhat, _
        After:=LastCell, _
        LookIn:=LookIn, _
        LookAt:=XLookAt, _
        SearchOrder:=SearchOrder, _
        MatchCase:=MatchCase)

If Not FoundCell Is Nothing Then
    Set FirstFound = FoundCell
    'Set ResultRange = FoundCell
    'Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Do Until False ' Loop forever. We'll "Exit Do" when necessary.
        Include = False
        If BeginsWith = vbNullString And EndsWith = vbNullString Then
            Include = True
        Else
            If BeginsWith <> vbNullString Then
                If StrComp(Left(FoundCell.Text, Len(BeginsWith)), BeginsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
            If EndsWith <> vbNullString Then
                If StrComp(Right(FoundCell.Text, Len(EndsWith)), EndsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
        End If
        If Include = True Then
            If ResultRange Is Nothing Then
                Set ResultRange = FoundCell
            Else
                Set ResultRange = Application.Union(ResultRange, FoundCell)
            End If
        End If
        Set FoundCell = SearchRange.FindNext(After:=FoundCell)
        If (FoundCell Is Nothing) Then
            Exit Do
        End If
        If (FoundCell.Address = FirstFound.Address) Then
            Exit Do
        End If

    Loop
End If
    
Set FindAll = ResultRange

End Function

 

[CordingBags]

This nearly works, but the value to search for is held in "Current cell" and the sheet name to exclude is "Current sheet"

Such that:
' CHANGE THIS VALUE TO THE VALUE TO BE SEARCHED FOR.
varValue = "1"
Needs the 1 to refer to Current Cell

' CHANGE THIS SHEET NAME TO THE ONE TO BE EXCLUDED FROM THE SEARCH.
Sheets("FirstSheet").Activate
Needs the First Sheet to refer to Current Sheet

Is this possible?
Thanks

 

[HighAndWilder]

This nearly works, but the value to search for is held in "Current cell" and the sheet name to exclude is "Current sheet"

Such that:
' CHANGE THIS VALUE TO THE VALUE TO BE SEARCHED FOR.
varValue = "1"
Needs the 1 to refer to Current Cell

' CHANGE THIS SHEET NAME TO THE ONE TO BE EXCLUDED FROM THE SEARCH.
Sheets("FirstSheet").Activate
Needs the First Sheet to refer to Current Sheet

Is this possible?
Thanks

Try this:

You need to insert the name of the sheet containing the value to be searched for on this line.
Sheets("FirstSheet").Activate

Just replace the 'subFindInSheets' procedure.

Public Sub subFindInSheets()
Dim rng As Range
Dim rngFound As Range
Dim Ws As Worksheet
Dim WsActive As Worksheet
Dim WsDestination As Worksheet
Dim s As String
Dim intRow As Integer
Dim intSheets As Integer
Dim intCells As Integer
Dim varValue As Variant
Dim blnFound As Boolean

  ActiveWorkbook.Save
  
  ' CHANGE THIS SHEET NAME TO THE ONE CONTAINING THE VALUE TO BE SEARCHED FOR.
  Sheets("FirstSheet").Activate
  
  ' CHANGE THIS VALUE TO THE VALUE TO BE SEARCHED FOR.
  varValue = ActiveCell.Value

  Set WsActive = ActiveSheet
  
  Application.DisplayAlerts = False
  On Error Resume Next
  Worksheets("Duplicates").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  
  Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Duplicates"

  Worksheets("Duplicates").Range("A1:B1").Value = Array("Sheet", "Cell")

  intRow = 2

  For Each Ws In Worksheets
      
    If Ws.Name <> WsActive.Name And Ws.Name <> "Duplicates" Then
  
      Set rngFound = FindAll(Ws.Range("A1").CurrentRegion.Columns("B:B"), varValue, xlValues, xlWhole, xlByColumns, True, , , vbTextCompare)
      
      If Not rngFound Is Nothing Then
      
        blnFound = True
      
        intSheets = intSheets + 1
    
        For Each rng In rngFound.Cells
          Worksheets("Duplicates").Cells(intRow, 1).Resize(1, 2) = Array(Ws.Name, rng.Address)
          intRow = intRow + 1
          intCells = intCells + 1
        Next rng
          
      End If

    End If

  Next Ws
  
  If blnFound Then
    MsgBox "The value of " & varValue & " has been found " & intCells & " times  in " & intSheets & " worksheets.", _
      vbOKOnly, "Confirmation"
  Else
    MsgBox "The value of '" & varValue & "' has NOT been found.", vbOKOnly, "Whoops!"
  End If
  
End Sub

 

[CordingBags]

Many Thanks for your help which works fine for individual entries, but becomes inefficient when there are 106 entries to check on each sheet and 17 sheets to cross reference each time an entry is made.
My solution was to create a helper table, replicating the data from every sheet into a single column on one of the less used sheets with each sheet cell that needs checking referring to this with a normal VLOOKUP formula as the entries are made.
Again Many Thanks for your efforts
Cheers
Paul