What function

  • Thread starter Thread starter soconfused
  • Start date Start date
S

soconfused

I have two sheets and I need to compare a list that's in the column of "R" on
the first sheet to the same list, same colum in another sheet. I just need
to make sure the second sheet has the same numbers and if it doesn't I'd like
the cell to say something like "checked"
 
If you want the 2 columns to be identical, and only have it show if they
aren't, then you can use conditional formatting to change the color of the
cell or font to let you know that they are different.

In the conditional format menu, leave the first entry as Cell Is, then type
the following into the equqtion box:

<>Sheet1!R1

Then select the format to turn the cell color black for instance so you know
which cells dont match up
 
I don't think I stated my problem correctly. The information on the second
sheet is going to change on a monthly basis. If ABC, DEF, GHI are on the
sheet from the previous month, they may not be on this months and that's what
I need to know. I guess I need to compare the two columns and make a remark
if there is information that isn't there from the previous month.
 
I am sorry, but if you are using Excel 2003, than you cant use conditional
formatting referencing another worksheet, maybe Excel 2007 lets you.

In this case you probably have to create a column next to the one you are
looking at (column R). In column S you can write the following formula in S1
and copy all the way down column S:

IF($R1=Sheet1!$R1,$R1,"checked")
 
I need something a little bit more than that as the information will not be
in the same row as last month.

For example

April May
92284 92285
92285 92342
92342 92344
92343 92347
 
Let's assume your range of values from the prior month is on a sheet named
'Prior' in column B. Add a column next to your current month values and use
this formula:

=IF(COUNTIF(Prior!B:B,A1)>0,"CHECK","NOT FOUND")

The countif just counts how many records it finds in a range that match
specified criteria.

This formula assumes that your prior month values are on a sheet named
"Prior" in column B, and that your current month values start in cell A1 of a
different sheet.
 
Mike,

I think I am almost there, here is the formula I modified to fit my
worksheet, but I'm still not getting the right answer.

=IF(COUNTIF('Prior Month'!$A$1:$A$26,$Q$5)>0,"Checked", "")

In the prioir month worksheet are the serial numbers for the prior month.
The $Q$5 refers to the start of the column to be checked in the current
month. I am getting "Checked" for everyone, but some should be blank.

Do you know what I'm doing wrong?

Thanks.
 
Back
Top