countif using range in conditional formatting

[lisach]

Several inventory spreadsheets in a workbook, a new one added each week w/ current inventory. Column A=item numbers with a range name. I then use conditional formatting to highlight all cells containing new items. ie.. new arrivals and did not exist in previous week's sheet.

I do get some cells to highlight correctly and I thought it was working but then I find SOME of those item numbers do exist in the previous weeks sheet. So..... what have I done wrong?

Very simple but oh so elusive!
So many questions and so many helpful people here that I can't seem to find this.
Thank-you in advance.

[BB1972]

lisach,

Maybe you could throw us a bone and identify some of the problem cells?

[FDibbins]

change the CF formula from =COUNTIF(newlist0530,A2)=0 to =COUNTIF(newlist0530,A3)=0

Also, I would question the need to use a new sheet for each week? You could probably just as easily use 1 sheet and use countifS() for a date range in the CF

[lisach]

OMG! So sorry. Yes.
sheet 6-27-13
line 31 highlighted in green is not present on sheet 5-30-13
line 32. highlighted in green is present on sheet 5-30-13
line 128 highlighted in green is present on sheet 5-30-13

and it goes on. when i filter those cells in green, I see some that are new and some that are not new

[FDibbins]

did you read my post?

[lisach]

FDibbins....
I made the suggested change and that seems to have done the trick. could you please explain how that made a difference? I am not understanding the significance of the change.

to answer your question. I receive the new sheets in email from a supplier and my associate likes to see the new sheet as a stand alone. I just hate trying to figure out what items are new.

[lisach]

FDibbins...
The change you suggested seems to work on the 6-27-13 sheet (didn't check each and every one) But, going on the assumption that this would apply to each of the sheets I went back to the 5-30-13 sheet. I filtered the cells based on the green color. I changed the CF with the adjustment to A3 and the results were impacted. The result included items that are present on the preceding 05-20 sheet.
Line 161 item M170-144 should not have been highlighted green as it does appear on the 05-20 sheet.
Line 268 item M150-855 should not have been highlighted green as it does appear on the 05-20 sheet

thoughts suggestions????

[FDibbins]

The mistake you made in the 1st sheet you mentioned was because you started the CF in row 3, but referenced row 2, so the CF was always looking at the row above to test its criteria.

I will take a look at the other sheets

[FDibbins]

OKJ looking at the 1st 3 date sheets, the data all starts in row 2, so the CF formula on those sheets was correct. It's only on the last sheet that you have an extra line above the data and below the headingf (row 2)

[lisach]

I see why you said that. I attached another book showing a compare and cells that were formatted green that shouldn't be and cells tat weren't formatted that should have been. Please take a look at this workbook and let me know. Maybe the countif is not the right way to do it. I'm pretty confused at this point.

[FDibbins]

the range you are using in the CF is for 520, but you are comparing 530 to 627

[lisach]

I created the range newlist0627. I select the range. I choose CF new rule. use formula to determine which cells to format. The formula I use is... =COUNTIF(newlist0530,A3)=0

on sheet 05-30 I created the range newlist0530. Select the range. choose CF and use formula...... =COUNTIF(newlist0520,A2)=0

On each new sheet the "newlist" range of item numbers should format in green those cells containing item numbers that do not exit in the previous sheet. Thereby showing me those new items.

Or at least that is how I intended for it to work out. Not sure what you meant by ..."the range you are using in the CF is for 520"

[FDibbins]

I have taken a slightly different approach and combined all data onto 1 sheet. I started the CF in row 453 and used...
=COUNTIF($B$2:B453,B453)=1

All you now need to do when you have more data, is increase the APPLIES to range, and add dates in (new) column A

take a look, check it and let me know if this is something you can work with?