Alternate Row Shading

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Alternate Row Shading

WA.TWORSX via AOL
AOO415m1(Build:9789)  -  Rev. 1817496
2017-12-11 17:25

WIN10-64 Desktop

___________________________________________________________

I find that often the template files available from
https://www.vertex42.com/ make use of alternate row shading, and I would
like to also. Example (copied from a CALC spreadsheet):






Does anybody know of a technique of placing shading on alternate rows in
CALC, as shown above?

Does this require use of macros or is there a function that can
accomplish this?

____________________________________________________________

Implementing alternate row shading in my spreadsheets might lead to some
difficulties, since I often use a sorting routine.  I will address that
issue at a later time.

Regards,

VinceB.


Reply | Threaded
Open this post in threaded view
|

Re: Alternate Row Shading

WA.TWORSX via AOL

Please, Goto:
https://www.vertex42.com/ExcelTemplates/asset-tracking-software.html to
be able to see an example of alternate row shading.


On 5/31/2018 12:32 PM, WA.TWORSX via AOL wrote:

> AOO415m1(Build:9789) -  Rev. 1817496
> 2017-12-11 17:25
>
> WIN10-64 Desktop
>
> ___________________________________________________________
>
> I find that often the template files available from
> https://www.vertex42.com/ make use of alternate row shading, and I
> would like to also. Example (copied from a CALC spreadsheet):
>
>
>
>
>
>
> Does anybody know of a technique of placing shading on alternate rows
> in CALC, as shown above?
>
> Does this require use of macros or is there a function that can
> accomplish this?
>
> ____________________________________________________________
>
> Implementing alternate row shading in my spreadsheets might lead to
> some difficulties, since I often use a sorting routine.  I will
> address that issue at a later time.
>
> Regards,
>
> VinceB.
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Alternate Row Shading

Brian Barker-2
In reply to this post by WA.TWORSX via AOL
At 12:32 31/05/2018 -0400, Vince Bonly wrote:
>I find that often the template files available from
>https://www.vertex42.com/ make use of alternate row shading, and I
>would like to also. Example (copied from a CALC spreadsheet):

If you included an image here, it will have been removed by the
mailing list processor before your message was delivered. But I'm
sure readers will understand what you mean.

>Does anybody know of a technique of placing shading on alternate
>rows in CALC, as shown above? Does this require use of macros or is
>there a function that can accomplish this?

o Click the rectangle at top left where the row and column headers
meet (or go to Edit | Select All, or press Ctrl+A) to select the
entire sheet (or as appropriate).
o Go to Format | Conditional Formatting... .
o For Condition 1, select "Formula is".
o In the box, enter MOD(ROW();2) .
o Click New Style... .
o On the Organiser tab, against Name, enter your new style's name -
perhaps "Shading" or "Pink"?
o On the Background tab, choose your background colour.
o OK.
o OK.

This gives shading on odd rows. If you want even rows shaded, change
the formula to MOD(ROW();2)-1 . By ticking Condition 2 and adding
another cell style, you can arrange to have both odd and even rows
shaded but differently.

>Implementing alternate row shading in my spreadsheets might lead to
>some difficulties, since I often use a sorting routine.

Sorting data should not disturb this shading technique.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Alternate Row Shading

WA.TWORSX via AOL


On 5/31/2018 1:06 PM, Brian Barker wrote:

> At 12:32 31/05/2018 -0400, Vince Bonly wrote:
>> I find that often the template files available from
>> https://www.vertex42.com/ make use of alternate row shading, and I
>> would like to also. Example (copied from a CALC spreadsheet):
>
> If you included an image here, it will have been removed by the
> mailing list processor before your message was delivered. But I'm sure
> readers will understand what you mean.
>
>> Does anybody know of a technique of placing shading on alternate rows
>> in CALC, as shown above? Does this require use of macros or is there
>> a function that can accomplish this?
>
> o Click the rectangle at top left where the row and column headers
> meet (or go to Edit | Select All, or press Ctrl+A) to select the
> entire sheet (or as appropriate).
> o Go to Format | Conditional Formatting... .
> o For Condition 1, select "Formula is".
> o In the box, enter MOD(ROW();2) .
> o Click New Style... .
> o On the Organiser tab, against Name, enter your new style's name -
> perhaps "Shading" or "Pink"?
> o On the Background tab, choose your background colour.
> o OK.
> o OK.
>
> This gives shading on odd rows. If you want even rows shaded, change
> the formula to MOD(ROW();2)-1 . By ticking Condition 2 and adding
> another cell style, you can arrange to have both odd and even rows
> shaded but differently.
>
>> Implementing alternate row shading in my spreadsheets might lead to
>> some difficulties, since I often use a sorting routine.
>
> Sorting data should not disturb this shading technique.
>
> I trust this helps.
>
> Brian Barker
>

TNX again Brian; your suggestion worked perfectly.
I thought for a moment that my question was improperly phrased, as I
want the alternate row shading NOT done to an entire sheet. So, your
"(or as appropriate)" gave me confidence to enter the formula into
B3:G15. Worked like a charm!

Thee remains a problem, however.  When attempting to sort with extended
selection data contained within B2:C14:
G L
A L
C O
D F
F N
L S
N V
O A
Q S
S Q
S W
V X
W D


the data within B2;C2 is not being included during the sorting
execution.  I have seen this happen previously (shading not involved). 
Any ideas on this?

Regards,
VinceB.

P.S. My pasting of B2:C14 data above might not appear? So I first pasted
the data into Notepad and then copied from Notepad to paste it below:

G    L
A    L
C    O
D    F
F    N
L    S
N    V
O    A
Q    S
S    Q
S    W
V    X
W    D

Please note that "G" and "L" (present within B2;C2) are not being
included by the sorting routine for some reason. Is this a known issue?

Reply | Threaded
Open this post in threaded view
|

Re: Alternate Row Shading

WA.TWORSX via AOL

Go figure: If I copy/paste empty cells from CALC into my email client
(Mozilla Thunderbird), it is removed; but if data is present within the
cells, that is not removed?


Reply | Threaded
Open this post in threaded view
|

Re: Alternate Row Shading

Russell Munk
In reply to this post by WA.TWORSX via AOL

WA.TWORSX via AOL wrote on 6/1/2018 9:00 AM:

> [...]
> Thee remains a problem, however.  When attempting to sort with
> extended selection data contained within B2:C14:
> G     L
> A     L
> C     O
> D     F
> F     N
> L     S
> N     V
> O     A
> Q     S
> S     Q
> S     W
> V     X
> W     D
>
>
> the data within B2;C2 is not being included during the sorting
> execution.  I have seen this happen previously (shading not
> involved).  Any ideas on this?
>
> Regards,
> VinceB.
> [...]
It sounds like row 2 is being treated as a header. So in the Sort dialog
on the Options tab be sure 'Range contains column labels' is unchecked.
- Russ


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Sorting and column labels [was: Alternate Row Shading]

Brian Barker-2
In reply to this post by WA.TWORSX via AOL
At 11:00 01/06/2018 -0400, Vince Bonly wrote:
>TNX again Brian; your suggestion worked perfectly.

Good-oh!

>There remains a problem, however. When attempting to sort with
>extended selection data contained within B2:C14:
>G       L
>A       L
>C       O
>[...]
>
>the data within B2;C2 is not being included during the sorting
>execution. I have seen this happen previously (shading not
>involved). Any ideas on this?

Yup. On the Options tab of the Sort dialogue, you will see "Range
contains column labels". If this is ticked, the top row of the block
of values (for sorting rows, or leftmost column for sorting columns)
will be teated as labels - in other words names for the columns (or
rows) of actual data - rather than part of the data themselves. In
your case, the G and the L are being treated as labels for columns B
and C. Remove the tick and you will get what you want.

Note that Calc appears to set this option intelligently - perhaps
assuming that text values - your G and L - are likely to be labels. I
find it is always necessary to check this option before clicking OK
to carry out the sort process. I suppose the proper way to do things
is first to give all your columns labels in the preceding row and
then always to include that label row in the range you sort. Calc
should make the appropriate decision to tick that option and
everything will be hunky-dory.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Pasting spreadsheet data [was: Alternate Row Shading]

Brian Barker-2
In reply to this post by WA.TWORSX via AOL
At 11:04 01/06/2018 -0400, Vince Bonly wrote:
>Go figure: If I copy/paste empty cells from CALC into my email
>client (Mozilla Thunderbird), it is removed; but if data is present
>within the cells, that is not removed?

When you copy something, a lot of incidental information is included.
You can save cells copied from a spreadsheet as a table in a text
document, for example. What happens in general depends on what
options you select (e.g. via Paste Special...) and what the target
document can accept. I suspect your mail client will be offered tab
characters between cells, so that empty cells would result in
consecutive tab characters. Your mail client may or may not recognise
tab characters; it may treat them in different ways; whether it
transmits them as part of the mail message may depend on your choice
of mail format (e.g. HTML versus plain text ); how recipients' mail
clients handle the result may differ!

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re; Sorting and Column Labels [WAS: Re: Alternate Row Shading]

WA.TWORSX via AOL
In reply to this post by Russell Munk


On 6/1/2018 11:26 AM, Russell Munk wrote:

>
> WA.TWORSX via AOL wrote on 6/1/2018 9:00 AM:
>> [...]
>> Thee remains a problem, however.  When attempting to sort with
>> extended selection data contained within B2:C14:
>> G     L
>> A     L
>> C     O
>> D     F
>> F     N
>> L     S
>> N     V
>> O     A
>> Q     S
>> S     Q
>> S     W
>> V     X
>> W     D
>>
>>
>> the data within B2;C2 is not being included during the sorting
>> execution.  I have seen this happen previously (shading not
>> involved).  Any ideas on this?
>>
>> Regards,
>> VinceB.
>> [...]
> It sounds like row 2 is being treated as a header. So in the Sort dialog
> on the Options tab be sure 'Range contains column labels' is unchecked.
> - Russ

TNX Russ for drawing my attention to the Sort dialog Options tab.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Sorting and column labels [was: Alternate Row Shading]

WA.TWORSX via AOL
In reply to this post by Brian Barker-2


On 6/1/2018 11:41 AM, Brian Barker wrote:

>
>> [...]
>>
> Yup. On the Options tab of the Sort dialogue, you will see "Range
> contains column labels". If this is ticked, the top row of the block
> of values (for sorting rows, or leftmost column for sorting columns)
> will be teated as labels - in other words names for the columns (or
> rows) of actual data - rather than part of the data themselves. In
> your case, the G and the L are being treated as labels for columns B
> and C. Remove the tick and you will get what you want.
>
> Note that Calc appears to set this option intelligently - perhaps
> assuming that text values - your G and L - are likely to be labels. I
> find it is always necessary to check this option before clicking OK to
> carry out the sort process. I suppose the proper way to do things is
> first to give all your columns labels in the preceding row and then
> always to include that label row in the range you sort. Calc should
> make the appropriate decision to tick that option and everything will
> be hunky-dory.
>
> I trust this helps.
>
> Brian Barker
>
Thanks to Russ and to you, Brian, I believe that I have learned the
lesson about the status of the Options tab's "Range contains column
la_b_els" box.

I just happen to use alpha characters within my previously pasted
example; they were not meant to be "labels" or "headers".

I have just tested what CALC does when the topmost row contains an alpha
character vs contains a numerical character....  CALC senses if the
topmost data is indeed alpha or numeric; and if numeric, the Options
tab's "Range contains column la_b_els" box is, initially,  unchecked. 
Therefore, I think when doing a sort routine it is not necessary to
check the status of that box if the topmost row's data is not numerical
character(s).

Of course, if/whenever I forget to check the status in the future, the
resultant sorted data is rather easy to recognize as being incorrectly
sorted. In those instances, I should then recall that the state of the
"Range contains column la_b_els" box needs to be validated.


VinceB.