OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to conditional format the Sub Total Table ?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Seng Kuang
General User
General User


Joined: 18 Aug 2007
Posts: 36
Location: Malaysia

PostPosted: Sat Sep 15, 2007 9:42 am    Post subject: How to conditional format the Sub Total Table ? Reply with quote

Dear Sir,

I have the following sub total table:


Subtotal Table
A B C D
1 Area Product Date Amount
2 K252 654P 25/7/07 500
3 K252 247N 25/7/07 250
4 K252 987L 25/7/07 350
5 K252 Sub total 1100
6 P318 247N 25/7/07 470
7 P318 987L 25/7/07 380
8 P318 Sub total 850
9 F241 654P 25/7/07 220
10 F241 247N 25/7/07 350
11 F241 987L 25/7/07 260
12 F241 874F 25/7/07 380
13 P241 Sub total 1210
14 G018 663K 25/7/07 410
15 G018 664U 25/7/07 550
16 G018 141T 25/7/07 420
17 P241 Sub total 1380
GRAND TOTAL 4540


May I know how to conditional format the Sub total rows with light yellow
highlighting and red bold font ?

What formula must I key in after highlighting the entire block and clicking
at Format > Conditional Formatting ?


Thanks

Low
_________________
APX25GPL
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Sep 15, 2007 10:59 am    Post subject: Reply with quote

Call the stylist (F11).
Select a sub-total cell and look which cell-style has been assigned automatically.
Right-click that cell style, Modify...
Set this according to your needs.

If this does not meet your requirements:
Select the range and notice which column has the SUBTOTAL formula.
Format>Conditional...
Formula: ISNUMBER(FIND("SUBTOTAL";FORMULA($XY))
Style: any style you want.

$XY means exactly column X (the SUBTOTAL-column) in the same row Y as the currently active input cell.
Current cell in row 1 and subtotal formula in column B:
ISNUMBER(FIND("SUBTOTAL";FORMULA($B1))
Back to top
View user's profile Send private message
Seng Kuang
General User
General User


Joined: 18 Aug 2007
Posts: 36
Location: Malaysia

PostPosted: Sat Sep 15, 2007 1:12 pm    Post subject: Reply with quote

Helo Villeroy,

First I higlight K252 Sub total at Cell A5 with read and set the font bold.
After that, I highlight A1:D18 (whole table) and click Format > Conditional Format and enter the formula ISNUMBER(FIND("SUBTOTAL";FORMULA($XY))

and select F11 as the style but there is no change all the sub total lines.

When this feature is functioning all the lines of Subtotal and the total figure should have the format of F11.

Earlier on I set F11 by going to Format > Style and Formating > New Stye > Name F11 and mofify the propeties to red bold font with light yellow shadiing.

May I know if I am doing the right steps ?

Thanks

Low
_________________
APX25GPL
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Sep 15, 2007 1:45 pm    Post subject: Reply with quote

Quote:
First I higlight K252 Sub total at Cell A5 with read and set the font bold.
After that, I highlight A1:D18 (whole table) and click Format > Conditional Format and enter the formula ISNUMBER(FIND("SUBTOTAL";FORMULA($XY))

Hard formatting overrides styles.

Quote:
... and select F11 as the style ...

I referred to "F11" as the key keyboard key F11 which pops up the stylist. Obviously you have created a new style named "F11". That's fine, but it could have a more meaningful name. Probably you did not set any formatting attributes for that style.

Create a second version of your file (save as other file), just in case.
Select the range and clear all hard formatting (Ctrl+Shift+Space).
Data>Refresh should re-apply some built-in style to the sub-total rows. You can see which style has been applied when you select a cell, having that style. The style gets highlighted in the stylist.

Don't override the styles by hard formatting. Just change the style itself.
The entire Office suite is based on styles. This is no harassment. It is a rather simple concept, which saves a lot of work once you got used to it.
I hardly ever call formatting dialogs. I simply double-click my own prepared styles (named sets of formatting attributes). Only if I want to highlight a particular cell or word I use hard formatting as exception to the general formatting rules.

EDIT: It's the built-in style "Result" that applies to sub-totals. Simply set it's properties and clear all hard formatting. No need for a conditional style.
Back to top
View user's profile Send private message
Seng Kuang
General User
General User


Joined: 18 Aug 2007
Posts: 36
Location: Malaysia

PostPosted: Sat Sep 15, 2007 7:41 pm    Post subject: Reply with quote

Dear Villeroy,

I tried to modify the style of "ResulT" after F11, but could not set the font color as the button is off. Also I tried Data > Refresh Range but could not proceed as the button is always off. < could not be clicked at>

I still opted to format manually as it is faster.

Thank you anyway for your explanation.

Low
_________________
APX25GPL
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Sep 15, 2007 10:54 pm    Post subject: Reply with quote

http://www.mediafire.com/?afjdefwysy9
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group