1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

how to compare two cells on two different sheets in Excel

Discussion in 'Business Applications' started by BellaNotte, Jun 16, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. BellaNotte

    BellaNotte Thread Starter

    Joined:
    Jun 16, 2005
    Messages:
    2
    Hi everyone

    I've been having an Excel problems, and i've tried experimenting on my own, and searching the forums here, but I didn't find anything that appeared relevant so I'm posting now. I apologise if this has come up before.

    Using Excel 2000, I have two sheets (A & B) in a work book that contain lots of data, all in different places. I need to be able to compare any two cells on these two sheets, and enter if they match on another sheet (c). The cells being compared will not be the same on both sheets (e.g. I need to compare P2336 and B12). To add complication, the values on sheet B are all derived via formulas from a fourth sheet, D.

    I can't get If statements to work, conditional formats won't work across two sheets, and the instructions for Vlookup and its relations just confuse me.

    Does anyone know how to compare two individual cells on different sheets, and enter the results of the comparison (match/error or True/False, it doesn't really matter as long as we can see what the result is)?

    Many thanks to everyone for all your time
    Best Regards
    Notty
     
  2. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    hi Bellanotte,

    everything you say does work across sheets.

    the reference to another sheet is to precede your cell reference with the sheet name and an !

    e.g. from sheet1, to reference cell a3 on sheet2 you would code sheet2!a3

    so there is no prob on say sheet3 having a formula in a cell such as:

    =if(sheet1!p2336=sheet2!b12,"Same","NotSame")

    lol
    Hew
     
  3. BellaNotte

    BellaNotte Thread Starter

    Joined:
    Jun 16, 2005
    Messages:
    2
    hi

    I've justtried that and I'm getting #name? errors. Thanks anyway though. Can you think of anything else?

    Best regards
    Notty
     
  4. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi BellaNote,

    Are you typing the sheet names as my example? you have to type the name as it appears on the sheet tabs at the bottom of excel, i.e. don't use sheet2 if your sheet is actaullly called MySheetName.
    #Name indicates you have an invalid reference, you have typed the cell ref incorrect or are trying to ref an undefined named range.

    Try using the click on cell method instead (this is how I would do it anyway)

    instead of typing the cell reference preceded by the sheetname! :-

    on the formula bar type =if(
    then at that point click on the cell in the first sheet that you want to compare, (this will put that cell refence into the formula for you)
    then type =
    then at that point click on the cell in the other sheet that you want to compare, (this will put that cell refence into the formula for you)
    then type ,"Same","NotSame")

    Compare the cell references 'created' this way with what you have been typing.

    lol
    Hew
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/372143

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice