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
|