Applying Formulas to Visible Cells Only

I'm trying to create a helper column in CA to help me conditional format.

For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)

Since I'm working wtih filtered data, there are a lot of hidden rows -- I
want the formula to only apply or interact with other visible cells, so I'm
able to conditional format visible cells alternate colors.

Is there away around this? Thanks...


Value I am getting in CA
Value I want
Row 177 x Apples 1 1
Row 180 x Banannas 1 0
Row 183 x Banannas 1 0
Row 191 x Pears 1
1
Row 200 x Monkeys 1 0
Row 202 x Monkeys 1 0
Row 205 x Giraffes 1
1

If you have Excel 2003, the SUBTOTAL function will do the trick:

=MOD(SUBTOTAL(103,$A$1:$A$1000),3)

HTH,
Elkar



"SteveC" wrote:

> I'm trying to create a helper column in CA to help me conditional format.
>
> For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
>
> Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> want the formula to only apply or interact with other visible cells, so I'm
> able to conditional format visible cells alternate colors.
>
> Is there away around this? Thanks...
>
>
> Value I am getting in CA
> Value I want
> Row 177 x Apples 1 1
> Row 180 x Banannas 1 0
> Row 183 x Banannas 1 0
> Row 191 x Pears 1
> 1
> Row 200 x Monkeys 1 0
> Row 202 x Monkeys 1 0
> Row 205 x Giraffes 1
> 1
>
>

Thanks... I apply that formula in CA:177 and I get zeros, instead of 0s and
1s... not sure what I'm doing wrong...

"Elkar" wrote:

> If you have Excel 2003, the SUBTOTAL function will do the trick:
>
> =MOD(SUBTOTAL(103,$A$1:$A$1000),3)
>
> HTH,
> Elkar
>
>
>
> "SteveC" wrote:
>
> > I'm trying to create a helper column in CA to help me conditional format.
> >
> > For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> > OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
> >
> > Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> > want the formula to only apply or interact with other visible cells, so I'm
> > able to conditional format visible cells alternate colors.
> >
> > Is there away around this? Thanks...
> >
> >
> > Value I am getting in CA
> > Value I want
> > Row 177 x Apples 1 1
> > Row 180 x Banannas 1 0
> > Row 183 x Banannas 1 0
> > Row 191 x Pears 1
> > 1
> > Row 200 x Monkeys 1 0
> > Row 202 x Monkeys 1 0
> > Row 205 x Giraffes 1
> > 1
> >
> >

If copied down through Column CA, you should get a series of:

0
2
1
0
2
1
0
2
1
Etc...

You can then apply conditional formatting to all 0's or 1's or 2's, your
preference. Thus giving the effect of every 3rd row being shaded. I think
that's what you're looking for?

If all you're getting are 0's, then perhaps you're using an older version of
Excel?


"SteveC" wrote:

> Thanks... I apply that formula in CA:177 and I get zeros, instead of 0s and
> 1s... not sure what I'm doing wrong...
>
> "Elkar" wrote:
>
> > If you have Excel 2003, the SUBTOTAL function will do the trick:
> >
> > =MOD(SUBTOTAL(103,$A$1:$A$1000),3)
> >
> > HTH,
> > Elkar
> >
> >
> >
> > "SteveC" wrote:
> >
> > > I'm trying to create a helper column in CA to help me conditional format.
> > >
> > > For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> > > OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
> > >
> > > Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> > > want the formula to only apply or interact with other visible cells, so I'm
> > > able to conditional format visible cells alternate colors.
> > >
> > > Is there away around this? Thanks...
> > >
> > >
> > > Value I am getting in CA
> > > Value I want
> > > Row 177 x Apples 1 1
> > > Row 180 x Banannas 1 0
> > > Row 183 x Banannas 1 0
> > > Row 191 x Pears 1
> > > 1
> > > Row 200 x Monkeys 1 0
> > > Row 202 x Monkeys 1 0
> > > Row 205 x Giraffes 1
> > > 1
> > >
> > >

Hi, thanks...

I'm using Excel 2003.

I'm not trying to format every 3rd row (I'm trying to alternatively shade
rows that have identical values in Colum B13:B3000).

But regardless, I suppose your formula should still work and I can't figure
out why it's not. I have a feeling I may not be referencing the correct
column. Just to clarify, I changed your formula to
=MOD(SUBTOTAL(103,$B$13:$B$3000),3) but it didn't work...didn't work with the
original =MOD(SUBTOTAL(103,$A$1:$A$1000),3) either...

Anyway thanks for the suggestions... I have a feeling it's some
configuration of subtotal...



"Elkar" wrote:

> If copied down through Column CA, you should get a series of:
>
> 0
> 2
> 1
> 0
> 2
> 1
> 0
> 2
> 1
> Etc...
>
> You can then apply conditional formatting to all 0's or 1's or 2's, your
> preference. Thus giving the effect of every 3rd row being shaded. I think
> that's what you're looking for?
>
> If all you're getting are 0's, then perhaps you're using an older version of
> Excel?
>
>
> "SteveC" wrote:
>
> > Thanks... I apply that formula in CA:177 and I get zeros, instead of 0s and
> > 1s... not sure what I'm doing wrong...
> >
> > "Elkar" wrote:
> >
> > > If you have Excel 2003, the SUBTOTAL function will do the trick:
> > >
> > > =MOD(SUBTOTAL(103,$A$1:$A$1000),3)
> > >
> > > HTH,
> > > Elkar
> > >
> > >
> > >
> > > "SteveC" wrote:
> > >
> > > > I'm trying to create a helper column in CA to help me conditional format.
> > > >
> > > > For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> > > > OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
> > > >
> > > > Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> > > > want the formula to only apply or interact with other visible cells, so I'm
> > > > able to conditional format visible cells alternate colors.
> > > >
> > > > Is there away around this? Thanks...
> > > >
> > > >
> > > > Value I am getting in CA
> > > > Value I want
> > > > Row 177 x Apples 1 1
> > > > Row 180 x Banannas 1 0
> > > > Row 183 x Banannas 1 0
> > > > Row 191 x Pears 1
> > > > 1
> > > > Row 200 x Monkeys 1 0
> > > > Row 202 x Monkeys 1 0
> > > > Row 205 x Giraffes 1
> > > > 1
> > > >
> > > >

Oops, I see the problem. I wasn't paying close enough attention when typing
in the formula here. The beginning of the range should be a relative
reference.

=MOD(SUBTOTAL(103,B13:$B$3000),3)

Although, thats not what you're looking for. Perhaps something along the
lines of:

=IF(COUNTIF($B$13:$B$3000,B13)>1,0,1)

But that will find all duplicates, hidden or not. I'm going to have to give
this one some more thought. I'll post back if I come up with anything.

Elkar

"SteveC" wrote:

> Hi, thanks...
>
> I'm using Excel 2003.
>
> I'm not trying to format every 3rd row (I'm trying to alternatively shade
> rows that have identical values in Colum B13:B3000).
>
> But regardless, I suppose your formula should still work and I can't figure
> out why it's not. I have a feeling I may not be referencing the correct
> column. Just to clarify, I changed your formula to
> =MOD(SUBTOTAL(103,$B$13:$B$3000),3) but it didn't work...didn't work with the
> original =MOD(SUBTOTAL(103,$A$1:$A$1000),3) either...
>
> Anyway thanks for the suggestions... I have a feeling it's some
> configuration of subtotal...
>
>
>
> "Elkar" wrote:
>
> > If copied down through Column CA, you should get a series of:
> >
> > 0
> > 2
> > 1
> > 0
> > 2
> > 1
> > 0
> > 2
> > 1
> > Etc...
> >
> > You can then apply conditional formatting to all 0's or 1's or 2's, your
> > preference. Thus giving the effect of every 3rd row being shaded. I think
> > that's what you're looking for?
> >
> > If all you're getting are 0's, then perhaps you're using an older version of
> > Excel?
> >
> >
> > "SteveC" wrote:
> >
> > > Thanks... I apply that formula in CA:177 and I get zeros, instead of 0s and
> > > 1s... not sure what I'm doing wrong...
> > >
> > > "Elkar" wrote:
> > >
> > > > If you have Excel 2003, the SUBTOTAL function will do the trick:
> > > >
> > > > =MOD(SUBTOTAL(103,$A$1:$A$1000),3)
> > > >
> > > > HTH,
> > > > Elkar
> > > >
> > > >
> > > >
> > > > "SteveC" wrote:
> > > >
> > > > > I'm trying to create a helper column in CA to help me conditional format.
> > > > >
> > > > > For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> > > > > OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
> > > > >
> > > > > Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> > > > > want the formula to only apply or interact with other visible cells, so I'm
> > > > > able to conditional format visible cells alternate colors.
> > > > >
> > > > > Is there away around this? Thanks...
> > > > >
> > > > >
> > > > > Value I am getting in CA
> > > > > Value I want
> > > > > Row 177 x Apples 1 1
> > > > > Row 180 x Banannas 1 0
> > > > > Row 183 x Banannas 1 0
> > > > > Row 191 x Pears 1
> > > > > 1
> > > > > Row 200 x Monkeys 1 0
> > > > > Row 202 x Monkeys 1 0
> > > > > Row 205 x Giraffes 1
> > > > > 1
> > > > >
> > > > >

I've spent way too much time today searching newsgroups for this and I just
can't find anything I can use (or more likely, understand)... thanks for
giving it a shot!

"Elkar" wrote:

> Oops, I see the problem. I wasn't paying close enough attention when typing
> in the formula here. The beginning of the range should be a relative
> reference.
>
> =MOD(SUBTOTAL(103,B13:$B$3000),3)
>
> Although, thats not what you're looking for. Perhaps something along the
> lines of:
>
> =IF(COUNTIF($B$13:$B$3000,B13)>1,0,1)
>
> But that will find all duplicates, hidden or not. I'm going to have to give
> this one some more thought. I'll post back if I come up with anything.
>
> Elkar
>
> "SteveC" wrote:
>
> > Hi, thanks...
> >
> > I'm using Excel 2003.
> >
> > I'm not trying to format every 3rd row (I'm trying to alternatively shade
> > rows that have identical values in Colum B13:B3000).
> >
> > But regardless, I suppose your formula should still work and I can't figure
> > out why it's not. I have a feeling I may not be referencing the correct
> > column. Just to clarify, I changed your formula to
> > =MOD(SUBTOTAL(103,$B$13:$B$3000),3) but it didn't work...didn't work with the
> > original =MOD(SUBTOTAL(103,$A$1:$A$1000),3) either...
> >
> > Anyway thanks for the suggestions... I have a feeling it's some
> > configuration of subtotal...
> >
> >
> >
> > "Elkar" wrote:
> >
> > > If copied down through Column CA, you should get a series of:
> > >
> > > 0
> > > 2
> > > 1
> > > 0
> > > 2
> > > 1
> > > 0
> > > 2
> > > 1
> > > Etc...
> > >
> > > You can then apply conditional formatting to all 0's or 1's or 2's, your
> > > preference. Thus giving the effect of every 3rd row being shaded. I think
> > > that's what you're looking for?
> > >
> > > If all you're getting are 0's, then perhaps you're using an older version of
> > > Excel?
> > >
> > >
> > > "SteveC" wrote:
> > >
> > > > Thanks... I apply that formula in CA:177 and I get zeros, instead of 0s and
> > > > 1s... not sure what I'm doing wrong...
> > > >
> > > > "Elkar" wrote:
> > > >
> > > > > If you have Excel 2003, the SUBTOTAL function will do the trick:
> > > > >
> > > > > =MOD(SUBTOTAL(103,$A$1:$A$1000),3)
> > > > >
> > > > > HTH,
> > > > > Elkar
> > > > >
> > > > >
> > > > >
> > > > > "SteveC" wrote:
> > > > >
> > > > > > I'm trying to create a helper column in CA to help me conditional format.
> > > > > >
> > > > > > For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> > > > > > OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
> > > > > >
> > > > > > Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> > > > > > want the formula to only apply or interact with other visible cells, so I'm
> > > > > > able to conditional format visible cells alternate colors.
> > > > > >
> > > > > > Is there away around this? Thanks...
> > > > > >
> > > > > >
> > > > > > Value I am getting in CA
> > > > > > Value I want
> > > > > > Row 177 x Apples 1 1
> > > > > > Row 180 x Banannas 1 0
> > > > > > Row 183 x Banannas 1 0
> > > > > > Row 191 x Pears 1
> > > > > > 1
> > > > > > Row 200 x Monkeys 1 0
> > > > > > Row 202 x Monkeys 1 0
> > > > > > Row 205 x Giraffes 1
> > > > > > 1
> > > > > >
> > > > > >

Assuming that A2:B100 contains the unfiltered data, try the following...

1) Select A2:B100, making sure that A2 is the active cell

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula...

=MOD(SUBTOTAL(3,$A$2:$A2),2)=1

This will format every other row, starting with the first one. If you
want to start with the second row, replace =1 with =0.

4) Choose your formatting

5) Click Ok

Hope this helps!

In article <[email protected]>,
SteveC <[email protected]> wrote:

> I'm trying to create a helper column in CA to help me conditional format.
>
> For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
> OR($A177<>OFFSET($A177,-1,0), $B177<>OFFSET($B177,-1,0)),2)
>
> Since I'm working wtih filtered data, there are a lot of hidden rows -- I
> want the formula to only apply or interact with other visible cells, so I'm
> able to conditional format visible cells alternate colors.
>
> Is there away around this? Thanks...
>
>
> Value I am getting in CA
> Value I want
> Row 177 x Apples 1 1
> Row 180 x Banannas 1 0
> Row 183 x Banannas 1 0
> Row 191 x Pears 1
> 1
> Row 200 x Monkeys 1 0
> Row 202 x Monkeys 1 0
> Row 205 x Giraffes 1
> 1