Excel 2007 : Comparisons in Excel 2007?

I'm hoping someone can help me.

I need Excel to take a number in sheet A, and if that number appears on Sheet B, I need it to return a yes, or a true, anything really.

Any way to do this?? I can do one to one comparison, but I can't find the logical argument for "if it appears anywhere on the sheet".

Oh please help!! =)

Thanks!!

~C~

If you are OK with using a UDF (User Defined Function) then the following - placed in a standard Module (i.e., not in one of the worksheet modules or the ThisWorkbook module) - will work:

Please Login or Register  to view this content.

To use this, type the following into a cell on a worksheet:

=FoundItThere("SearchedWorksheetName", 10.2)

where SearchedWorksheetName is the name of the worksheet you want to search (and yes, the name needs to be in double quotes) and 10.2 is the value for which you are searching. An alternate way is to use

=FoundItThere("SearchedWorksheetName", A13)

where cell A13 on the worksheet you are entering the formula in has the value 10.2 in it - either as a number or the result of a calculation. You don't have to use cell a13 - that's just an example.

Note that this finds exact matches. If a cell in SearchedWorkbookName has a value of 10.19993 and is formatted to show just one decimal place, it will look like it has 10.2 in it, but FoundItThere will returne False because the value isn't exactly 10.2. You could modify FoundItThere to check to see if any values in SearchedWorkbookName are within some small percentage of the sought value, but it seems that you are after exact matches.

Also note that as written FoundItThere will find text matches - but only if the entire contents of the cell in SearchedWorkbookName match exactly (including capitalization) the value in A13.

There may be some issues with the size of the range returned by .UsedRange being bigger than it appears it should be - but it still should be faster than checking every cell in the worksheet.

The line:

Application.Volatile (True)

makes Excel recalculate every occurance of FoundItThere any time Excel recalculates anything in the workbook. If this causes too much lag, then if you are very consistant in selecting the cell you used FoundItThere in (or A13, if you used that method) and press F2 (Edit) and then the Enter key, you can manually force FoundItThere to recalculate whenever you want to. Note that without the Application.Volatile (True) line, F9 won't cause FoundItThere to recalculate...

Originally Posted by DonkeyOte

Perhaps:

SheetA!B1: =IF(COUNTIF(SheetB!$1:$65536,$A1),"Yes","No")

However by referencing the entire sheet you're creating lots of dependencies - ie changing any cell on SheetB will flag all formulae in Column A on SheetA to recalculate which may slow your file in the long run... if you can reduce the range do so (ie A:Z etc...)

This worked like a charm, thank you so much!!!!