Conditional formatting afraid of cell formulas

Discuss the spreadsheet application

Conditional formatting afraid of cell formulas

Postby simtug » Wed Feb 01, 2012 5:48 pm

When a formula displays a value, conditional formatting should apply a user defined style to the cell which includes a higher character height and higher text distance from cell border values.

It doesn't work (unless you manually type a value in the cell instead of letting this happen via formula).

Any suggestion?


PS. Check attached "test.ods" file with simple explanation reproducing the above behaviour.
Update 2012-02-03: "Aiming for result.ods" file gives you more of a "real life" example of what I am trying to do with Calc.
Attachments
Aiming for result.ods
(15.62 KiB) Downloaded 56 times
Test updated.ods
(14.07 KiB) Downloaded 66 times
Last edited by simtug on Fri Feb 03, 2012 3:55 pm, edited 2 times in total.
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby qaz1qaz1qa » Wed Feb 01, 2012 6:15 pm

Your example says to type something in B5 and nothing happens
However B3 does work with your special event format.

B8 contains a simple formula (copies value from B5 if B5 has any) but conditional formatting doesn't work UNLESS you type something on the cell OR you open the conditional formatting dialogue window. Why?


When I open your table the Baby Blue bar is at B3 and B6. B6 has the formula
Code: Select all   Expand viewCollapse view
=IF(B3="";"";B3)


These 2 cells work fine.
Only thing is your row 6 is too short and you can't see the font.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Conditional formatting afraid of cell formulas

Postby simtug » Wed Feb 01, 2012 6:41 pm

Thank you, qaz1qaz1qa, I have updated the test file with coherent cell references within the description!

Cells work fine, yes, but row 6 SHOULD open up and display the value based on the user defined style (which is the same of the input cell). So, conditional formatting is not able to impose a style which has to do with row height, but in my understanding it should..... why does it not?
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby qaz1qaz1qa » Wed Feb 01, 2012 9:17 pm

What I see happen is this
The reference cell updates and the row responds
The formula cell now calculates and does all it needs to do but this is after all the rows have done their work and so is too late to receive the change.

So i recorded a macro and associated it with Sheet Events > Content Changed
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****


sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------SELECT THE ENTIRE SHEET--------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

rem -----------------------AUTO ROW HIGHT-----------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "aExtraHeight"
args2(0).Value = 0

dispatcher.executeDispatch(document, ".uno:SetOptimalRowHeight", "", 0, args2())

rem ----------------------DESELECT by moving nowhere-----------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 0
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())

rem ----------------------------------------------------------------------



end sub


Note if you associate this with Selection Change it will loop as the macro changes selection.
Association with Formulas Calculated loops also.

If you still have trouble place
Wait 100
Just below the sub Main
Change 100 to what will work best for you.
Last edited by qaz1qaz1qa on Wed Feb 01, 2012 9:42 pm, edited 1 time in total.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Conditional formatting afraid of cell formulas

Postby MrProgrammer » Wed Feb 01, 2012 9:36 pm

My best guess is that the problem the OP has noticed is a known issue: No screen refresh for conditional formatting with formulas
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 2044
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Conditional formatting afraid of cell formulas

Postby kingfisher » Thu Feb 02, 2012 1:55 am

The change occurs if you save then use the menu selection File > Reload.
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2015
Joined: Tue Nov 20, 2007 10:53 am

Re: Conditional formatting afraid of cell formulas

Postby simtug » Thu Feb 02, 2012 5:14 pm

Thanks all for your kind and prompt replies... yet not fully satisfied, almost but not fully... here is why....

qaz1qaz1qa wrote:The reference cell updates and the row responds
The formula cell now calculates and does all it needs to do but this is after all the rows have done their work and so is too late to receive the change.

So i recorded a macro and associated it with Sheet Events > Content Changed

Your logic is perfect, qaz1qaz1qa, but I hardly have the possibility to insert a macro: 1. I am not at ease with them (I was in the past with Excel, but not yet in Calc); 2. my users have high security ON preventing macros to work; 3. it is not a final solution. And yet, you are most welcome, qaz1qaz1qa, and clapping to your expertise.



MrProgrammer wrote:My best guess is that the problem the OP has noticed is a known issue: No screen refresh for conditional formatting with formulas

Seems to be similar to this issue.



kingfisher wrote:The change occurs if you save then use the menu selection File > Reload.

:super:
You are 100% correct. This works.... :arrow: but... how can I tell unknown end users "save the file first and then reload it?"..... any suggestion on how to have this trick work automatically is highly hoped for.....
;)
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby MrProgrammer » Thu Feb 02, 2012 6:33 pm

Another potential issue is: Row height for formula cell doesn't adapt automatically if content in referenced cell changes

But you may also have problems with your SpecialEvent style. If I create a new style from scratch and use Format > Conditional Formatting with the new style instead of SpecialEvent it seems to work better, though the row height still isn't adjusted as the issue above notes. I'd suggest that you get rid of SpecialEvent and rebuild it, especiallly if this file has ever been saved by Excel.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 2044
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Conditional formatting afraid of cell formulas

Postby simtug » Thu Feb 02, 2012 6:45 pm

Row height is the main issue here.

I would be accomodated somehow if I could have File>Save and File>Reload commands be executed automatically. How could this trick be handled? A macro.... (hope not...high security may not let it work)?
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby qaz1qaz1qa » Fri Feb 03, 2012 12:11 am

I am one who does not like an auto save. If I am working on a project and want to undo, that is gone once the reload is hit. I strongly suggest you do not make a macro or automate a series of this type.

As far as I comprehend Features beyond calculating an manipulating information moves into the macro category.
Untill this ... I'll call it a deficiency is addressed it might have to be tolerated if you are leery of macros. You can get a signature and add them to the macro. Then your friends will not be so wary. I am not at this time aware of how to do so.

Other Options are:
Not use a text so large
Horizontaly center the text so It will always show.
Preset the row Hights.

If I was more aware of your purpose in needing such a large font I might be of more help.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Conditional formatting afraid of cell formulas

Postby simtug » Fri Feb 03, 2012 12:58 am

I agree on all points.

At first I didn't consider saving does delete operations' hystory. Not viable.

Signature + macro... beyond my current ability.

Macro without signature: might be the only way, then. End users should be informed of this necessity (read "flaw of OO") aforetime.

I will give a try to your macro, qaz1qaz1qa, and let you know of the outcome.

Considering other preformatting features is always my first choice, but in this case the end users have a simple sheet ready for printing (set to fit a paper size with weekly events displayed according to current date) with plenty of info on usual events (source of info a database-like sheet). When special events are found in the database sheet all normal info go blank and the previously low blank row shows up with few details about the special event in vey big letters (other rows would be affected by another conditional formatting and a user defined style and should become very low, does preserving the paper size printing of the sheet including all decorating borders). Do you get an idea of what I'm doing?


Conditional formatting and style would work smoothly to achieve my goal... but they don't...

Should any other forum user come up with a genial idea... don't be shy...
;)
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby kingfisher » Fri Feb 03, 2012 4:06 am

Are the same rows always involved? If so, you could create a template with those rows heightened.
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2015
Joined: Tue Nov 20, 2007 10:53 am

Re: Conditional formatting afraid of cell formulas

Postby simtug » Fri Feb 03, 2012 12:23 pm

kingfisher wrote:Are the same rows always involved? If so, you could create a template with those rows heightened.

Thank you, kingfisher, for your help! Problem is the printed sheet has got 5 weeks of schedule one after the other, each week contained in, say, 10 rows. I added an 11th row in between. This 11th row in case of Special Event should "pop up", or better said "stand up" as the only information available for that week in very large and bold characters, leaving the other 10 rows blank and flattened to a very small seize so as not to modify the overall space reserved for that random week. Other weeks in the same sheet of paper will not be affected by the Special Event effect.

Now, building a template could mean building 5 different sheets, one for each individual week and have the end user know he needs to check out if there are special events planned for the upcoming five weeks and print ONLY the sheet with the Special Effect.... I bet it would get confusing, at least not "user friendly", to say the least...

Or am I getting you wrong, kingfisher?... maybe I don't see the suggestion as practical as it could be.....? :shock:
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby kingfisher » Fri Feb 03, 2012 12:52 pm

You're the best judge of that.
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2015
Joined: Tue Nov 20, 2007 10:53 am

Re: Conditional formatting afraid of cell formulas

Postby simtug » Fri Feb 03, 2012 3:38 pm

kingfisher wrote:You're the best judge of that.

Thumbs up, kingfisher! :super:

:idea:
Should there be a different way to get the same result (one single sheet, easy to open, easy to print for newbie end users) I would be more than happy to evaluate it...


Attached an almost "real life" sheet displaying what I am trying to achieve. :D
Attachments
Aiming for result.ods
(15.62 KiB) Downloaded 58 times
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby qaz1qaz1qa » Fri Feb 03, 2012 6:38 pm

I was thinking the other day how I handle my macro security.
I have my macros only enabled if a file is loaded from a specific location.
Tools > Options
+OpenOffice.org > Security - [Macro security] .. /Trusted Source\ > Trusted File Location [ADD]

THe macro I created is simply a recording of Key board strokes.

Ctrl+A
Alt - o - r - o - [Enter]
▲▼ (to deselect)

Optionaly on any toolbar you can click the ▼ > Customize toolbar .. [Add] .. Format > Optimal Row Height [Add] [ok][ok]

Then all you have to do is hit Ctrl+A ( or click the top left unlabeled square) and then press the button.

So the Macro is only a fast automated way of doing what OO can already do.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Conditional formatting afraid of cell formulas

Postby qaz1qaz1qa » Fri Feb 03, 2012 7:04 pm

:lol: you put the "Special Event" text on the same line so yes it does auto adjust. :knock:
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: Conditional formatting afraid of cell formulas

Postby kingfisher » Sat Feb 04, 2012 1:43 am

Same result here. Deleting the content of F19 automatically results in the row height being reduced to the default value and, of course, nothing is displayed in B19.
Apache OpenOffice 4.1.1 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2015
Joined: Tue Nov 20, 2007 10:53 am

Re: Conditional formatting afraid of cell formulas

Postby simtug » Sun Feb 05, 2012 12:03 am

Hi guys. It's incredible what has happened... (Murphy law) :shock:

I posted the file Aiming for result.ods after creating it from scratch, but.... without checking what would happen when deleting the "Special Event" statement, assuming it would show the already described OO falt (NEVER "assume" in the tech world, never).

Then I read your replies. I was simply in awe..... :shock: :knock:

Thank you qaz1qaz1qa and thank you kingfisher for checking the file!!!


So here we are, guys, "unexpected" correct behaviour of Calc. So this time I created a new file (guess what, I named it "To be or not to be a working style.ods") with three sheets. In the first sheet everything works fine. In the second and third sheets things get stuck!

So I assume (did I already say "never assume".....?!) conditional formatting works fine with styles affecting row heights when all happens within the same sheet. Conditional formatting and styles show flaws when reading input values from a different sheet.
:ouch:


Do you, wonderful guys, have anything to add to my "assumptions"?
Attachments
To be or not to be a working style.ods
(20.21 KiB) Downloaded 43 times
OpenOffice 3.3.0 on Windows 7 64 bit
User avatar
simtug
 
Posts: 16
Joined: Wed Feb 01, 2012 5:14 pm

Re: Conditional formatting afraid of cell formulas

Postby qaz1qaz1qa » Sun Feb 05, 2012 3:35 am

I am not sure you read correct the above text.
Yes this is because on the first page the text deleted IS IN THE SAME row and thus you are manipulating the very row that needs to resize and so the program resizes it.
But on any other page you are not directly entering text in the row that is to resize. SO it does not resize.
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
User avatar
qaz1qaz1qa
 
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests