+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Comparisons in Excel 2007?

  1. #1
    Registered User
    Join Date
    05-12-2009
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question 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~

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Comparisons in Excel 2007?

    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...)

  3. #3
    Registered User
    Join Date
    05-12-2009
    Location
    Texas, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Comparisons in Excel 2007?

    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...

  4. #4
    Registered User
    Join Date
    05-12-2009
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Thumbs up Re: Comparisons in Excel 2007?

    Quote Originally Posted by DonkeyOte View Post
    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!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1