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

--
Message posted via http://www.officekb.com

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


"Sam via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

On May 29, 1:39 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
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

--
Message posted viahttp://www.officekb.com


If your data start from, say, A2, then in FormatConditional
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

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

DazzaData wrote:
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

--
Message posted via http://www.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 wrote:
If your data start from, say, A2, then in FormatConditional
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

--
Message posted via http://www.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 wrote:
If your data start from, say, A2, then in FormatConditional
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200705/1

On May 29, 4:57 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
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 wrote:
If your data start from, say, A2, then in FormatConditional
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

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200705/1

Sam,

How about this for Formula Is:
=AND(MOD(ROW(),10)=MOD(COUNTA(A:A),10),ROW()<=COUN TA(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

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)


--
Regards

Roger Govier


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:72e739119c3d4@uwe...
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 wrote:
If your data start from, say, A2, then in FormatConditional
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200705/1

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

vezerid wrote:
Sam,

How about this for Formula Is:
=AND(MOD(ROW(),10)=MOD(COUNTA(A:A),10),ROW()<=COU NTA(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

--
Message posted via http://www.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

Roger Govier wrote:
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)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200705/1

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


"Sam via OfficeKB.com" wrote:

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

DazzaData wrote:
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

--
Message posted via http://www.officekb.com

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.

"DazzaData" wrote:

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


"Sam via OfficeKB.com" wrote:

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

DazzaData wrote:
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

--
Message posted via http://www.officekb.com

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

"Sam via OfficeKB.com" wrote:

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

DazzaData wrote:
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

--
Message posted via http://www.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

DazzaData wrote:
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

--
Message posted via http://www.officekb.com

On May 29, 8:57 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
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,
How about this for Formula Is:
=AND(MOD(ROW(),10)=MOD(COUNTA(A:A),10),ROW()<=COU NTA(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

--
Message posted viahttp://www.officekb.com

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

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()<=RO WS(Data)+1)

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

Cheers,
Sam

vezerid wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200705/1

On May 31, 3:50 am, "Sam via OfficeKB.com" <u4102@uwe wrote:
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()<=RO WS(Data)+1)

original:

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

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

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200705/1

Sam,
Glad it worked.

Kostis