2010/8/29 Andy Chaplin <[email protected]>: > On 08/29/2010 11:35 AM, Johnny Rosenberg wrote: >> >> 2010/8/29 Andy Chaplin<[email protected]>: >> >>> >>> On 08/28/2010 09:49 PM, Johnny Rosenberg wrote: >>> >>>> >>>> 2010/8/28 Andy Chaplin<[email protected]>: >>>> >>>> >>>>> >>>>> On 08/28/2010 05:45 PM, Johnny Rosenberg wrote: >>>>> >>>>> >>>>>> >>>>>> 2010/8/28 Jean-Baptiste Faure<[email protected]>: >>>>>> >>>>>> >>>>>> >>>>>>> >>>>>>> Le 28/08/2010 16:44, Andy Chaplin a écrit : >>>>>>> >>>>>>> >>>>>>> >>>>>>>> >>>>>>>> Hi all >>>>>>>> >>>>>>>> As far as I can see it's only possible to have 3 levels of >>>>>>>> conditional >>>>>>>> formatting. Does anyone know of a trick or workaround to get more >>>>>>>> conditions to apply. Ideally i would like six and any suggestions >>>>>>>> would be gratefully appreciated. >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> You can use formula with nested IF() and STYLE() functions. >>>>>>> >>>>>>> JBF >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> Absolutely. >>>>>> And if you want different formats for different text strings, such as >>>>>> names if people or something, you can create a style with the same >>>>>> name for each person and just add the style directly… For example: >>>>>> =Sheet1.A1 + STYLE(A1) >>>>>> Except that if Sheet1.A1 is empty, 0 will be displayed rather than an >>>>>> empty cell. >>>>>> >>>>>> The following might work: >>>>>> =IF(Sheet1.A1="";"";STYLE(A1)) >>>>>> >>>>>> But I don't know if this is doable in Andy’s situation. >>>>>> >>>>>> >>>>>> Regards >>>>>> >>>>>> Johnny Rosenberg >>>>>> >>>>>> --------------------------------------------------------------------- >>>>>> To unsubscribe, e-mail: [email protected] >>>>>> For additional commands, e-mail: [email protected] >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> Thanks to you both for your prompt replies (which actually solve >>>>> another >>>>> problem I was working on!) >>>>> >>>>> In this case, however, it doesn't seem to do what I want (which I >>>>> didn't >>>>> explain in my original query). >>>>> >>>>> What I have is a table with parts listed vertically and dates >>>>> horizontally. >>>>> Each part can be in 1 of 6 different states on any given day. What >>>>> I'm >>>>> trying to do is to set up a sheet whereby if you put, for example, A in >>>>> a >>>>> cell the backgound is red, B is blue, C is green, D is yellow and so >>>>> on. >>>>> >>>>> ... actually, as I've been typing this, I've worked out that this could >>>>> be >>>>> done using your proposal by having a data entry sheet and a separate >>>>> sheet >>>>> for display and nested IFs, but is there a way of doing this without >>>>> the >>>>> intermediate sheet? >>>>> >>>>> >>>> >>>> You don't need any IFs if you just name your styles properly, but I >>>> understand what you mean. Unfortunately I didn't find a solution to >>>> this problem when I experimented with this a few years ago, but if I >>>> recall correctly I filed a feature request about this, I think. >>>> >>>> If you are familiar with OpenOffice.org Basic programming you could >>>> write a macro that solves your problem. Since you only need six >>>> different states you could write a macro that does the following: >>>> — Open a dialogue box with six radio buttons marked A, B, C, D, E and F. >>>> — When you selected one of them by either click it or hit a key on >>>> your keyboard, for example A, and you then hit Enter or click OK, the >>>> letter will automatically be filled in the currently selected cell and >>>> the background colour will automatically be adjusted. >>>> — You could associate the macro with a keyboard combination, such as >>>> Ctrl+Alt+a. >>>> >>>> Another suggestion is to write six macros, one for each state. One >>>> fills in an A and the right colour for A, one does the same for B. No >>>> dialogue bos is then required and you associate the macros to six >>>> different keyboard combinations. I found that these six combinations >>>> are not taken, at least not on my system: >>>> Alt+a >>>> Alt+b >>>> Alt+c >>>> Alt+d >>>> Alt+e >>>> Alt+f >>>> >>>> Also, these combinations are not taken: >>>> Ctrl+Alt+a >>>> Ctrl+Alt+b >>>> Ctrl+Alt+c >>>> Ctrl+Alt+d >>>> Ctrl+Alt+e >>>> Ctrl+Alt+f >>>> >>>> >>>> Isn't that very convenient…? >>>> >>>> Here are the macros required, I have tested them. They also require >>>> that you create six styles named A, B, C and so on. That makes it more >>>> flexible, so you can change colours of backgrounds, characters etc, >>>> without having to change the code. >>>> >>>> REM ***** BASIC ***** >>>> >>>> Option Explicit >>>> >>>> Sub A >>>> setCellProperties("A") >>>> End Sub >>>> >>>> Sub B >>>> setCellProperties("B") >>>> End Sub >>>> >>>> Sub C >>>> setCellProperties("C") >>>> End Sub >>>> >>>> Sub D >>>> setCellProperties("D") >>>> End Sub >>>> >>>> Sub E >>>> setCellProperties("E") >>>> End Sub >>>> >>>> Sub F >>>> setCellProperties("F") >>>> End Sub >>>> >>>> Sub setCellProperties(State As String) >>>> Dim oCell As Object >>>> >>>> oCell=ThisComponent.getCurrentController().getSelection() >>>> If oCell.SupportsService("com.sun.star.sheet.SheetCell") Then ' >>>> Selection is a single cell >>>> oCell.setString(State) >>>> oCell.setPropertyValue("CellStyle",State) >>>> Else >>>> MsgBox "Please select one cell only!" >>>> EndIf >>>> End Sub >>>> >>>> >>>> >>>> So what happens, when you associate Alt+A with the macro A and so on, >>>> is that when you hit Alt+A, style A is applied to the selected cell >>>> and an ”A” is also entered in the cell automatically. So the extra >>>> work for you when you set everything up, is that you have to hold the >>>> Alt key when you enter your letter. Maybe that is an acceptable >>>> compromise? >>>> >>>> >>>> >>>> Kind regards >>>> >>>> Johnny Rosenberg >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: [email protected] >>>> For additional commands, e-mail: [email protected] >>>> >>>> >>>> >>>> >>> >>> WOW! >>> >>> Thank you so much for that. I've got a lot on my plate for today, but >>> I'll >>> try it out either this evening or tomorrow and get back to you. >>> >>> >> >> I am actually working on another solution right now with listeners. >> That would be very convenient if it works. There seems to be some >> known difficulties though, so I guess there need to be some ”ugly” >> code involved. >> >> The data you want to colour, where is it located? Is all of it in the >> same row or the same column? In that case, which one? The problem is >> that you can assign a listener to a specific cell, but then you need >> one listener per cell. You can also assign one listener to a specific >> cell range, but then you don't know which cell was changed, only that >> one (or more) in that range was changed, so I somehow need to compare >> ”before change” and ”after change” which could be time consuming. On >> the other hand, if the range isn't too big, the macro could just go >> through every cell in the range and set the colours accordingly. >> >> >> >> Regards >> >> Johnny Rosenberg >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> > > Hi there > > I have a file with around 20 sheets. Each sheet is an account. Each account > has between 10 and 30 parts listed vertically. An account period lasts for > no more than 100 days. The dates are listed horizontally. In this case the > primary use is to read the data in rows sow as to get a quick visual > overview of the status of each part. Each day is a snapshot, so once it's > entered it doesn't change. The object is to get a quick automatic > colour-coding of each entry. Up to now we've been using coloured strips on > a magnetic whiteboard, but the volume has increased dramatically in the past > few weeks and the only option now is to digitalise it. > > It looks like one of those things which should be quite straightforward, but > in practice is a bit tougher! ;o)
Seems a bit complicated… I'll take a look later. Meanwhile, I suggest everyone who wants more than 3 conditions to vote for this issue: http://qa.openoffice.org/issues/show_bug.cgi?id=8812 The issue was opened in 2002 and nothing happened so far! I don't have a clue why, since many people seems to want this feature and many can't live without it, as it seems, so they stay with Excel… I also saw that someone wrote some kind of add-on for this but I didn't look into it yet. Regards Johnny Rosenberg --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
