| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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)) |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
|
