[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Seng Kuang
General User

Joined: 18 Aug 2007
Posts: 36
Location: Malaysia

 Posted: Sat Sep 15, 2007 9:42 am    Post subject: How to conditional format the Sub Total Table ? 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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sat Sep 15, 2007 10:59 am    Post subject: 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))
Seng Kuang
General User

Joined: 18 Aug 2007
Posts: 36
Location: Malaysia

 Posted: Sat Sep 15, 2007 1:12 pm    Post subject: 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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Sat Sep 15, 2007 1:45 pm    Post subject:

 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.
Seng Kuang
General User

Joined: 18 Aug 2007
Posts: 36
Location: Malaysia

 Posted: Sat Sep 15, 2007 7:41 pm    Post subject: 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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sat Sep 15, 2007 10:54 pm    Post subject: http://www.mediafire.com/?afjdefwysy9
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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