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

Applying Formulas to Visible Cells Only
 

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

Applying Formulas to Visible Cells Only
 

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

Applying Formulas to Visible Cells Only
 

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

Applying Formulas to Visible Cells Only
 

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

Applying Formulas to Visible Cells Only
 

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

Applying Formulas to Visible Cells Only
 

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

Applying Formulas to Visible Cells Only
 

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 ,
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