how to compare two cells on two different sheets in Excel

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

 

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

 

hi

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

Best regards
Notty

 

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