Please Help

  • Thread starter Thread starter Steve Shipping
  • Start date Start date
S

Steve Shipping

I use a spreadsheet at work (Excel 97) to enter data on a daily basis, in
one of the columns I enter one of about six different notations (i.e.
DST654). I need to do a 10% audit of each of these and need a formula to
highlight each time the tenth one is entered. Can this be done?
 
Steve Shipping wrote...
I use a spreadsheet at work (Excel 97) to enter data on a daily basis, in
one of the columns I enter one of about six different notations (i.e.
DST654). I need to do a 10% audit of each of these and need a formula to
highlight each time the tenth one is entered. Can this be done?
Click to expand...

I'll assume you want to find these entries in column A from cell A2
down. I'll also assume you want to check these entries against a list
of codes, such as DST654 but also others, and I'll call this other list
CodesToFind. If you want to highlight every 10th entry in A2:A#
matching any of the entries in CodesToCheck, use conditional
formatting. Select the col A range with cell A2 active, run the menu
command Format > Conditional Formatting, change the dropdown list on
the left side to 'Formula Is', and in the field to the right enter the
formula

=AND(MOD(SUMPRODUCT(COUNTIF(CodesToFind,A$2:A2)),10)=0,
COUNTIF(CodesToFind,A2))

then choose the format you want to highlight these matches.
 
Back
Top