Here's a nice and simple UDF that works in a similar manner to the builtin SEARCH()
function, except it searches all the worksheets and returns the value of a different cell instead of the index:
'============================================================================================
' Module : <any standard module>
' Version : 0.1.0
' Part : 1 of 1
' References : N/A
' Source : https://superuser.com/a/1332265/763880
'============================================================================================
Option Explicit
Public Function SEARCHALLSHEETS _
( _
ByVal find_text As String, _
ByVal within_cell As Range, _
ByVal return_cell As Range _
) _
As Variant
Dim strWithinCell As String: strWithinCell = within_cell.Address
Dim strReturnCell As String: strReturnCell = return_cell.Address
Dim wkstWorksheet As Worksheet
For Each wkstWorksheet In within_cell.Parent.Parent.Worksheets
If InStr(wkstWorksheet.Range(strWithinCell), find_text) > 0 Then
Dim varReturnValue As Variant
varReturnValue = wkstWorksheet.Range(strReturnCell).Value2
Exit For
End If
Next wkstWorksheet
SEARCHALLSHEETS = IIf(IsEmpty(varReturnValue), CVErr(xlErrValue), varReturnValue)
End Function
Usage: SEARCHALLSHEETS(find_text, within_cell, return_cell)
Once it's installed, you use it by supplying the search target cell and return cell in any one worksheet of a workbook and it will search all the worksheets of that workbook.
If it can't find the search text in any of the worksheets, it returns the #VALUE!
error just like the SEARCH()
function does.
Your formula needs to be re-written to detect the error instead of a number to determine whether or not a match has been found. Plus, it also needs to be modified to use the new return value.
This is your updated formula:
=IFERROR(LEFT(RIGHT(SEARCHALLSHEETS(A3,[EDICONFTESTEXCEL.xlsm]Sheet1!$A$6,[EDICONFTESTEXCEL.xlsm]Sheet1!$A$7),9),7),A3)
There is an alternate, more direct conversion of your formula, but it is much longer. I have included it here for your reference:
=IF(NOT(ISERROR(SEARCHALLSHEETS(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7))),LEFT(RIGHT(SEARCHALLSHEETS(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7),9),7),A3)