I need to write an IF statement with Excel to do the following:
Take a value from a cell (say cell B2), see if this value occurs anywhere in column B on another worksheet and then, if it does leave the cell the formula is in blank and if it doesn't, put an * in the cell.
Looking at the IF statements dialog box I can see how to set the blank cell or the *, but I don't know how to make it compare the value in B2 with a list of values in column B of another sheet.
the suggestion from silasgreenback is probably a little tidier than mine...
But by way of explanation what mine does is use 3 functions,
MATCH takes the value in cell B2 and looks for it in Sheet2ColB. If it finds it the result is what position it is in in that column... i.e. effectively the row number...and ISNUMBER is therfore TRUE...
If it doesn't find it the return from MATCH is FALSE... i.e. it is not a number, so ISNUMBER returns FALSE.
I hope that makes sense...
ANd by the way I've just realised that in my previous post I said ROW when I should have said COL.. Sorry!!