Hi,

Pictures are worth a thousand words, so I am reposting an earlier question,
with a link to a sample file!

I have a weekly timekeeping report that shows about 250 lines for employee
time. This has 1-6 lines for each of employee.

This shows, among other things, the employee ID, name, # of hours worked per
day and overtime. The employee ID is a unique identifier.

Please see http://geocities.com/eadmati/Sample_Striping.xls for a sample
file which has the effect I am trying to automatically create.

I would like to have every other 'set' of the 1-6 lines of employee
information distinguished by striping, where the first set get striped, the
second gets not striped, the third one striped, and so on.

Sine I have manually created the effect, and there are 250 lines, this is
not feasible to do manually every week! I would like how to do this
automatically. I have been looking into conditional formatting, with varying
results. I suspect there are formulas needed as well.

The best way I can think of it is have a formula in a helper column that can
tell if each set of lines has the same employee ID, and mark that as a €˜1.
Then check if the next lines are the same, and mark as €˜0. Alternate that,
then I can use conditional formatting.

Thanks,

Eric

FYI that I know of no one around here is willing to open files posted as
there is just too much risk of viruses. That being said here is a way to do
what you have asked

I am assuming that you have a header row and so your names start in B2 with
your helper column starting in A2.
In Cell A2 type True (no quotes. it should be all caps when you hit enter)
In cell a3 add the formula
=IF(AND(A2,B3=B2),A2,IF(AND(NOT(A2),B3=B2),A2,NOT( A2)))
Copy the formula down. it should alternate TRUE and FALSE when names change.
For your conditional format you will use Formula Equals and just add the
formula
=$A2.
--
HTH...

Jim Thomlinson


"prana1" wrote:

Hi,

Pictures are worth a thousand words, so I am reposting an earlier question,
with a link to a sample file!

I have a weekly timekeeping report that shows about 250 lines for employee
time. This has 1-6 lines for each of employee.

This shows, among other things, the employee ID, name, # of hours worked per
day and overtime. The employee ID is a unique identifier.

Please see http://geocities.com/eadmati/Sample_Striping.xls for a sample
file which has the effect I am trying to automatically create.

I would like to have every other 'set' of the 1-6 lines of employee
information distinguished by striping, where the first set get striped, the
second gets not striped, the third one striped, and so on.

Sine I have manually created the effect, and there are 250 lines, this is
not feasible to do manually every week! I would like how to do this
automatically. I have been looking into conditional formatting, with varying
results. I suspect there are formulas needed as well.

The best way I can think of it is have a formula in a helper column that can
tell if each set of lines has the same employee ID, and mark that as a €˜1.
Then check if the next lines are the same, and mark as €˜0. Alternate that,
then I can use conditional formatting.

Thanks,

Eric

You are the man! That totally worked. Thank you for your help!

"Jim Thomlinson" wrote:

FYI that I know of no one around here is willing to open files posted as
there is just too much risk of viruses. That being said here is a way to do
what you have asked

I am assuming that you have a header row and so your names start in B2 with
your helper column starting in A2.
In Cell A2 type True (no quotes. it should be all caps when you hit enter)
In cell a3 add the formula
=IF(AND(A2,B3=B2),A2,IF(AND(NOT(A2),B3=B2),A2,NOT( A2)))
Copy the formula down. it should alternate TRUE and FALSE when names change.
For your conditional format you will use Formula Equals and just add the
formula
=$A2.
--
HTH...

Jim Thomlinson


"prana1" wrote:

Hi,

Pictures are worth a thousand words, so I am reposting an earlier question,
with a link to a sample file!

I have a weekly timekeeping report that shows about 250 lines for employee
time. This has 1-6 lines for each of employee.

This shows, among other things, the employee ID, name, # of hours worked per
day and overtime. The employee ID is a unique identifier.

Please see http://geocities.com/eadmati/Sample_Striping.xls for a sample
file which has the effect I am trying to automatically create.

I would like to have every other 'set' of the 1-6 lines of employee
information distinguished by striping, where the first set get striped, the
second gets not striped, the third one striped, and so on.

Sine I have manually created the effect, and there are 250 lines, this is
not feasible to do manually every week! I would like how to do this
automatically. I have been looking into conditional formatting, with varying
results. I suspect there are formulas needed as well.

The best way I can think of it is have a formula in a helper column that can
tell if each set of lines has the same employee ID, and mark that as a €˜1.
Then check if the next lines are the same, and mark as €˜0. Alternate that,
then I can use conditional formatting.

Thanks,

Eric