I wrote the following on Wed, 19 Jul 2006 12:30 -0700
> To all who have taken an interest in this topic.
>
> There is some "joy in mudville." I'm getting some conditional border setting
> to
> work. I plan to post again within the next 48 hours to give a final report
> that
> should include a HOW TO for this particular activity together with a warning
> as
> to what to avoid. I hope that my trials and tribulations in this endeavor may
> help some other poor soul in the future.
Here is the promised report. It missed its deadline by a few hours because I
needed to get some sleep. ;-)
What I originally tried to do was to modify a part of the document that I had
already created. That part of the document contained some cells for which I had
defined borders. I am calling that cell AM2 for the purposes of this report.
Another cell (AJ39) contains a text string that I want to use to suppress the
display of AM2 including its border. A third cell (B2) contains a value that I
want to display in AM2 unless the display of AM2 is being suppressed and I want
either of two different background colors for that value depending on whether
the value is a null string ("") or not.
Ok. That defines my starting point. Initially I tried to suppress the display
of the borders in AM2 by defining a creating a cell which had its borders
defined via "Line arrangement" "User-defined" and no borders selected. This did
not work. Then I tried "Line arrangement" "Default" "Set no borders." This also
did not work. For my conditional formatting I first tried "Format" "Conditional
formatting ..." "Condition 1" "Formula is" AJ30="No Show" "Cell style"
Borderless which did not work. I then tried the following snippet within the
cell content formula:
...IF(AJ39="No Show";T(STYLE("Borderless"));B2)...
As stated, this also did not work.
Here is what did work. I set the standard format for the cells in question (AM2
being one of them) to have no borders. I then defined a set of styles that had
the borders that I wanted and the backgrounds that I wanted. For my application
this involved creating 32 additional styles to address my needs in the 81 cells
that were involved. Two of those styles are "T40L40" and "T40L40S" with the
latter one having a background defined. The borders for these two styles
consisted of a 4.00 pt top border and a 4.00 pt left border. Having done this,
both of the following approaches worked for me though I prefer the first of the
two.
In the first approach, the cell content formula is
=IF(OR(AJ39="No Show";B2="");"";B2)
In the first approach, the Conditional Formats are defined as
Condition 1: Formula is: AND(NOT(AJ39="No Show");B2="") Cell style: T40L40
Condition 2: Formula is AND(NOT(AJ39="No Show");NOT(B2="")) Cell style T40L40S
In the second approach, the Conditional Format item on the Format menu is not
invoked but rather the cell content formula is:
=IF(AJ39="No
Show";"";IF(B2="";T(STYLE(T40L40));IF(ISNUMBER(B2);B2+STYLE(T40L40S);B2&T(STYLE(
T40L40S))))
As for the task of defining those 32 styles, I thought that I had to define
each one in the document itself and then invoke "Format" "Styles and
Formatting" to assign a name to that style. After that I figured that I needed
to go back to the document proper to change the sample cell that I had
formatted back to something more normal. I learned by poking ad prodding that
there is a much easier way to define a batch of styles.
>From *any* cell in the document and without altering that cell's format in any
way I invoked the Styles and Formatting window. F11 is an alternative way to
accomplish this. I then selected the "New style from selection" icon (second
from the right) and entered the name that I wanted for the new style (e.g.
T40L40). When I approved that I was returned to the list of styles with the new
style selected. I then right clicked on that style and selected "Modify" which
gave me access to all the formatting characteristics that are available. After
setting what I wanted I clicked OK to approve the format which again returned
me to the list of styles with the one I had just modified selected. Since all
of my styles shared certain characteristics I was able to again right click on
this style and select "New" from the menu to create another, related format.
All the options were there including the ability to name it as I pleased and to
link it to other formats if I also so pleased. This made the creation of the
other 31 styles relatively painless.
The rest of this message is addressed to Uwe Fischer who kindly asked for an
explanation of my frustration with OOo's Help. I hope it sheds light on a
novice user's frustrations for both him and others who support OOo's Help.
1) I could not find anything that stated that borders cannot be suppressed by
means of conditional formatting. As I stated earlier in this message, the cell
must be defined as having no borders in the first place if that option is to be
utilized for some condition. This is a significant restriction that needs to be
documented.
2) I could not find anything that told about the New.../Modify.../Delete...
menu from the style list in the "Styles and Formatting" window. The verbiage
dealing with styles or conditional formatting says:
===== Start of quoted material =====
Example of Conditional Formatting: Define Cell Styles
The next step is to apply a cell style to all values that represent above-
average turnover, and one to those that are below the average. Ensure that the
Styles and Formatting window is visible before proceeding.
1.Click in a blank cell and select the command Format Cells in the context
menu.
2.In the Format Cells dialog on the Background tab, select a background color.
Click OK.
3.In the Styles and Formatting window, click the New Style from Selection icon.
Enter the name of the new style. For this example, name the style "Above".
4.To define a second style, click again in a blank cell and proceed as
described above. Assign a different background color for the cell and assign a
name (for this example, "Below").
===== End of quoted material =====
3) The Help for STYLE() assumes that the value in the cell will be numeric so
it tells you to use +STYLE("stylename") which does not work if the cell content
is text. I could not find any mention is in Help as to how to use STYLE() for
text strings. This is not an issue for "Conditional Formatting..."
--
Jim
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
