Conditional Formatting - Draw Line every 10th Row

[Sam via OfficeKB.com]

Hi All,

I have a Dynamic named range called "Data", spanning 10 columns and many rows.
The range holds text values.

I would like 2 Conditional Formula's:

1) Draw a line across the 10th LAST row in my range "Data".
2) Draw a line across the 10th LAST row in my range "Data" and every 10th row
BACK thereafter.

Thanks
Sam

 

[Guest]

Hi,

Using a similar formula to the ones you are using to define the dynamic
range capture in a cell the row of the bottom of your range e.g. say row 256
(value x = 256) Also put the number of rows you want between underlines, 10
as an input, (y). and calculate the remainder in another cell mod (x,y) as
(z)

conditional formula for any row (R as a reference for the row were are in eg
A197) ) is = IF( AND( MOD( row(R)-z,y)=0 ), Row(R)<x) ,TRUE,FALSE)




For row numbers ending in a 6 this evaluates as TRUE and the underline will
be drawn. Row(R)<x just means it will not draw lines below the end of the
dataset.

For part a) simpler formula testing if R, the range we are in is 10 behind x

This will be a bit slow embedded into the conditional formula process itself
so it might be better to run these formula in cells in a hidden column,
evaluate to true false and then just set the conditional formula to look at
whether the corresponding cell for that row is true/false

 

[vezerid]

Hi All,

I have a Dynamic named range called "Data", spanning 10 columns and many rows.
The range holds text values.

I would like 2 Conditional Formula's:

1) Draw a line across the 10th LAST row in my range "Data".
2) Draw a line across the 10th LAST row in my range "Data" and every 10th row
BACK thereafter.

Thanks
Sam

If your data start from, say, A2, then in Format>Conditional
Formatting... Choose Formula Is and then enter the following:
=MOD(ROW()-ROW($A$2),10)=0
Then from Borders choose the bottom border.

HTH
Kostis Vezerides

 

[Sam via OfficeKB.com]

Hi DazzaData,

Thank you very much for your assistance.

I may have misunderstood your solution but

say row 256 (value x = 256)

I don't think this will work for my range. My range is Dynamic so x will
always be changing - not a static range.

Cheers Sam

Hi,

Using a similar formula to the ones you are using to define the dynamic
range capture in a cell the row of the bottom of your range e.g. say row 256
(value x = 256) Also put the number of rows you want between underlines, 10
as an input, (y). and calculate the remainder in another cell mod (x,y) as
(z)

conditional formula for any row (R as a reference for the row were are in eg
A197) ) is = IF( AND( MOD( row(R)-z,y)=0 ), Row(R)<x) ,TRUE,FALSE)




For row numbers ending in a 6 this evaluates as TRUE and the underline will
be drawn. Row(R)<x just means it will not draw lines below the end of the
dataset.

For part a) simpler formula testing if R, the range we are in is 10 behind x

This will be a bit slow embedded into the conditional formula process itself
so it might be better to run these formula in cells in a hidden column,
evaluate to true false and then just set the conditional formula to look at
whether the corresponding cell for that row is true/false

Hi All,

[quoted text clipped - 9 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Kostis,

Thank you very much for your assistance.

The Conditional Formula does work in that it provides a line across every
10th row. However, with my Dynamic Range a new row is always added to the
bottom of the Dynamic Range. For example Dynamic Range starts at $A$2. Today
$A$2:$A1000. Tomorrow $A$2:$A1001 etc. The range is forever growing. At the
moment, if I add a new row the CF does not move the lines up a row. Can the
CF take this into account.

Further assistance most appreciated.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Kostis,

Thank you very much for your assistance.

The Conditional Formula does work in that it provides a line across every
10th row. However, with my Dynamic Range a new row is always added to the
bottom of the Dynamic Range. For example Dynamic Range starts at $A$2. Today
$A$2:$A1000. Tomorrow $A$2:$A1001 etc. The range is forever growing. At the
moment, if I add a new row the CF does not move the lines up a row. Can the
CF take this into account.

Further assistance most appreciated.

Cheers,
Sam

 

[vezerid]

Hi Kostis,

Thank you very much for your assistance.

The Conditional Formula does work in that it provides a line across every
10th row. However, with my Dynamic Range a new row is always added to the
bottom of the Dynamic Range. For example Dynamic Range starts at $A$2. Today
$A$2:$A1000. Tomorrow $A$2:$A1001 etc. The range is forever growing. At the
moment, if I add a new row the CF does not move the lines up a row. Can the
CF take this into account.

Further assistance most appreciated.

Cheers,
Sam

Sam,

How about this for Formula Is:
=AND(MOD(ROW(),10)=MOD(COUNTA(A:A),10),ROW()<=COUNTA(A:A))

It assumes that your data range is contiguous and the you don't have
any other data below. Since the range can be growing I modified the
formula so that you can select the entire column A:A before applying
CF

HTH
Kostis

 

[Roger Govier]

Hi

Extend the selected range to which you apply conditional formatting, to
way beyond your expected growth in the dynamic range. Then modify
Kostis' formula to
=AND(A2<>"",MOD(ROW()-ROW($A$2),10)=0)

 

[Sam via OfficeKB.com]

Hi Kostis,

Thank you for reply.

The Formatting for the Dynamic range is not starting from the 10th LAST row;
it is now starting from the top / beginning of my range and selecting the
10th FIRST row and then every 10th row thereafter. I would like the
formatting of the Dynamic range to start from the 10th LAST row and every
10th row BACK thereafter. Is it possible?

Any further assistance much appreciated.

Cheers,
Sam

Sam,

How about this for Formula Is:
=AND(MOD(ROW(),10)=MOD(COUNTA(A:A),10),ROW()<=COUNTA(A:A))

 

[Sam via OfficeKB.com]

Hi Roger,

Thank you very much for your assistance.

Your modification of Kostis' Formula still leaves a problem of the CF
starting the line from the beginning of my range: 10th FIRST row and then
every 10th thereafter, rather than from the 10th LAST row and every 10th row
back thereafter.

Further assitance appreciated.

Cheers
Sam

 

[Guest]

Hi,

Yes, I suggest you post the method by which the dynamic range is defined,
presumably an offset formula or something. Insert-->Name-->Define, click on
the name of the range and tell us what it says in the refers to box

My idea is to use a variant of this formula to capture the number of the
last row in your data. Once supplied with this information, my formula can
then easily identify whether a row is a round 10 rows back from this row

Also don't forget my tip, put the test formula in row z or something. then
use conditional formula to just test if z for that row is true/false. If you
fill all the cells with a complex conditional formula it will run like a dog.

And you probably need to define a reasonable limit of rows that will ever be
used or again it will grind along

Cheers

Hi DazzaData,

Thank you very much for your assistance.

I may have misunderstood your solution but

say row 256 (value x = 256)

I don't think this will work for my range. My range is Dynamic so x will
always be changing - not a static range.

Cheers Sam

Hi,

Using a similar formula to the ones you are using to define the dynamic
range capture in a cell the row of the bottom of your range e.g. say row 256
(value x = 256) Also put the number of rows you want between underlines, 10
as an input, (y). and calculate the remainder in another cell mod (x,y) as
(z)

conditional formula for any row (R as a reference for the row were are in eg
A197) ) is = IF( AND( MOD( row(R)-z,y)=0 ), Row(R)<x) ,TRUE,FALSE)




For row numbers ending in a 6 this evaluates as TRUE and the underline will
be drawn. Row(R)<x just means it will not draw lines below the end of the
dataset.

For part a) simpler formula testing if R, the range we are in is 10 behind x

This will be a bit slow embedded into the conditional formula process itself
so it might be better to run these formula in cells in a hidden column,
evaluate to true false and then just set the conditional formula to look at
whether the corresponding cell for that row is true/false

Hi All,

[quoted text clipped - 9 lines]

Thanks
Sam

 

[Guest]

Also don't forget my tip, put the test formula in COLUMN z or something. then
use conditional formula to just test if COLUMN z for that row is true/false.
If you
fill all the cells with a complex conditional formula it will run like a dog.

Hi,

Yes, I suggest you post the method by which the dynamic range is defined,
presumably an offset formula or something. Insert-->Name-->Define, click on
the name of the range and tell us what it says in the refers to box

My idea is to use a variant of this formula to capture the number of the
last row in your data. Once supplied with this information, my formula can
then easily identify whether a row is a round 10 rows back from this row

Also don't forget my tip, put the test formula in row z or something. then
use conditional formula to just test if z for that row is true/false. If you
fill all the cells with a complex conditional formula it will run like a dog.

And you probably need to define a reasonable limit of rows that will ever be
used or again it will grind along

Cheers

Hi DazzaData,

Thank you very much for your assistance.

I may have misunderstood your solution but

say row 256 (value x = 256)

I don't think this will work for my range. My range is Dynamic so x will
always be changing - not a static range.

Cheers Sam

Hi,

Using a similar formula to the ones you are using to define the dynamic
range capture in a cell the row of the bottom of your range e.g. say row 256
(value x = 256) Also put the number of rows you want between underlines, 10
as an input, (y). and calculate the remainder in another cell mod (x,y) as
(z)

conditional formula for any row (R as a reference for the row were are in eg
A197) ) is = IF( AND( MOD( row(R)-z,y)=0 ), Row(R)<x) ,TRUE,FALSE)




For row numbers ending in a 6 this evaluates as TRUE and the underline will
be drawn. Row(R)<x just means it will not draw lines below the end of the
dataset.

For part a) simpler formula testing if R, the range we are in is 10 behind x

This will be a bit slow embedded into the conditional formula process itself
so it might be better to run these formula in cells in a hidden column,
evaluate to true false and then just set the conditional formula to look at
whether the corresponding cell for that row is true/false

Hi All,

[quoted text clipped - 9 lines]
Thanks
Sam

 

[Guest]

Yes, I suggest you post the method by which the dynamic range is defined,
presumably an offset formula or something. Insert-->Name-->Define, click on
the name of the range and tell us what it says in the refers to box

My idea is to use a variant of this formula to capture the number of the
last row in your data.

I should've said that by using a variant of the dynamic range formula the
value x will also be dynamic and always represent the correct value for the
last row

Hi DazzaData,

Thank you very much for your assistance.

I may have misunderstood your solution but

say row 256 (value x = 256)

I don't think this will work for my range. My range is Dynamic so x will
always be changing - not a static range.

Cheers Sam

Hi,

Using a similar formula to the ones you are using to define the dynamic
range capture in a cell the row of the bottom of your range e.g. say row 256
(value x = 256) Also put the number of rows you want between underlines, 10
as an input, (y). and calculate the remainder in another cell mod (x,y) as
(z)

conditional formula for any row (R as a reference for the row were are in eg
A197) ) is = IF( AND( MOD( row(R)-z,y)=0 ), Row(R)<x) ,TRUE,FALSE)




For row numbers ending in a 6 this evaluates as TRUE and the underline will
be drawn. Row(R)<x just means it will not draw lines below the end of the
dataset.

For part a) simpler formula testing if R, the range we are in is 10 behind x

This will be a bit slow embedded into the conditional formula process itself
so it might be better to run these formula in cells in a hidden column,
evaluate to true false and then just set the conditional formula to look at
whether the corresponding cell for that row is true/false

Hi All,

[quoted text clipped - 9 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi DazzaData,

Thanks for follow-up.

Below is my Dynamic Defined Named Range "Data" Refers To:
=OFFSET(Monthly!$H$12:INDEX(Monthly!$H$12:$H$3000,MATCH(REPT("z",255),Monthly!
$H$12:$H$3000)),0,0,,10)

Assistance very much appreciated.

Cheers,
Sam

Yes, I suggest you post the method by which the dynamic range is defined,
presumably an offset formula or something. Insert-->Name-->Define, click on
the name of the range and tell us what it says in the refers to box

 

[vezerid]

Hi Kostis,

Thank you for reply.

The Formatting for the Dynamic range is not starting from the 10th LAST row;
it is now starting from the top / beginning of my range and selecting the
10th FIRST row and then every 10th row thereafter. I would like the
formatting of the Dynamic range to start from the 10th LAST row and every
10th row BACK thereafter. Is it possible?

Any further assistance much appreciated.

Cheers,
Sam

vezeridwrote:

Sam,
the formula I gave you works from the bottom and up. I tested it.
Apparently I misunderstood your requirement and CF is now applied to
the LAST row, then LAST-10, etc. What you want is, if I understand
correctly, if your last data is in A899 then you want A889, A879 etc
to be bordered.

The CF formulas you need do not seem to be affected by your dynamic
range. Of course you can use ROWS(Data) instead of COUNTA(A:A)-1 but
that's about it. Apart from this you have to deal with MOD. I slightly
modified the formula to first format the 10th last row.

=AND(MOD(ROW(),10)=MOD(ROWS(Data),10),ROW()<=ROWS(Data)-10)

HTH
Kostis

 

[Sam via OfficeKB.com]

Hi Kostis,

Thank you so much for your help. Your Formula does the job Great!

I just needed to tweak it so the first bottom border line was drawn on
exactly the 10th (LAST) row from the bottom /end of my dynamic range. Works
Well - Thank you.

revised:

=AND(MOD(ROW(),10)=MOD(ROWS(Data)+1,10),ROW()<=ROWS(Data)+1)
original:
=AND(MOD(ROW(),10)=MOD(ROWS(Data),10),ROW()<=ROWS(Data)-10)

Cheers,
Sam

 

[vezerid]

Hi Kostis,

Thank you so much for your help. Your Formula does the job Great!

I just needed to tweak it so the first bottom border line was drawn on
exactly the 10th (LAST) row from the bottom /end of my dynamic range. Works
Well - Thank you.

revised:

Sam,
Glad it worked.

Kostis