How do I validate text in a cell based on other sheets in the work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I am trying to do is check the text in a cell on one sheet to see if it
is the same as the text in any of 4 cells on the last three sheets in the
workbook and issue a warning if it is the same. I can't seem to figure out a
way to do this. Can it be done and if so how?
 
Vague, but perhaps something simple along these lines
using COUNTIF would work for you

Assume the last 3 sheets are named: Sheet2, Sheet3, Sheet3
and the text to be checked in each are located in col A

In Sheet1,
you have the text* listed in A2 down
*to be checked

Put in B2:
=IF(SUM(COUNTIF(Sheet2!A:A,A2),COUNTIF(Sheet3!A:A,A2),COUNTIF(Sheet4!A:A,A2))>0,"Alert!","")
Copy B2 down as far as required. If the text in col A is found in any of the
3 sheets, col B will return: Alert!, otherwise it'll stay "blank". Adapt to
suit.
 
Did you try a formula like:
=OR(NOT(ISERROR(VLOOKUP(A1,Sheet2!
A1:A4,1,FALSE))),NOT(ISERROR(VLOOKUP(A1,Sheet3!
A1:A4,1,FALSE))),NOT(ISERROR(VLOOKUP(A1,Sheet4!A1:A4,1,FALSE))))

this gives you a FALSE if the word in A1 does not exist, or a TRUE if
it does exist.

Maybe that puts you on the right path.

Cheers Carlo
 
Back
Top