CODEWITHSUNDEEP
excel
This Is Stuffs
This Is Stuffs

Reputation: 21

Trying to make a function that determines if a string is present in another spreadsheet

Essentially, what I want is for the string to be checked if it occurs in another spreadsheet and to return TRUE or FALSE depending on if it is or not present in the other sheet. For checking if the single cell is exactly the same as another, the formula is relatively simple:

Example with A1 being checked in second sheet, same column and cell.

=AND(IF(A1=SheetX!A1, TRUE, FALSE),NOT(ISBLANK(A1)))

However, if you try to replace SheetX!A1 with SheetX!A1:A1001, for example, the response for true occurs in the row that the same value is found Sheet!X which I do not want, not mentioning the overflow problem (which I can deal with).

The only way I can think of fixing this is by using =OR and stating all the cells... in order... like this...

=OR(AND(IF(A1=Sheet2!$A$1,TRUE,FALSE),NOT(ISBLANK(A1))),AND(IF(A1=Sheet2!$A$2,TRUE,FALSE),NOT(ISBLANK(A1)),AND(IF(A1=Sheet2!$A$3,TRUE,FALSE),NOT(ISBLANK(A1)),AND(IF(A1=Sheet2!$A$4,TRUE,FALSE),NOT(ISBLANK(A1)))))

… etc

which works and gives the correct answer... but is... erm... to put in layman's terms... not feasible in the slightest.

There must be something I'm missing that's ridiculously easy to fix... but I can't find it after hours of wondering.

Many thanks to anyone who can spot the problem :)

Upvotes: 2

Views: 134

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider the following User Defined Function:

Public Function IsItInAnotherSheet(s As String, shname As String) As Boolean
    Application.Volatile
    Dim rng As Range, ws As Worksheet
    Set ws = Sheets(shname)
    Set rng = ws.Cells.Find(What:=s, after:=ws.Range("A1"))
    IsItInAnotherSheet = Not rng Is Nothing
End Function

For example:

enter image description here

Upvotes: 1

Related Questions