Applying Formulas to Visible Cells Only

[Guest]

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

 

[Guest]

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

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

HTH,
Elkar

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Domenic]

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!