It had to happen 🙂
Long story short, I just made a spreadsheet with a list of products in different states and I color coded them. I got 5 states. Conditional formatting only has 3 conditions. If I’d had 4 states, it would have been solved easy, by setting the default formatting for the cells to something and let that be the 4th status.
Now, I did some searches and first thing I found was some macro. Uhm… I don’t quite like to go into macros. Gets your document dependent on stuff. Nasty.
Some more searches showed the STYLE function. It returns 0 (zero). It’s only useful in formula cells. So what do you do if your cell contains some text that is not a formula? Or even if it IS a formula, but you don’t want to change it for whatever reason.
Well, Just move that column to somewhere else, and in place use a formula like:
=IF(STYLE(“Status-” &ROUND(F1))=0;G1; “error”)
BUT FIRST … you need to:
– create 5 styles names “Status-x”, where x ranges from 0 to 4. (Obviously you’ll adapt to your number of colors and naming preferences)
– create the $F column (well, you’ll just use this as helper) in which you will set your status, like 0, 1, 2, 3, 4
– have your $G column be the one with your needed values (the one you moved to, previously)
– and you’ll get “error” (put whatever you like here) in the column if something goes wrong. But the STYLE function will always return 0, even if no style defined, so that won’t help much. But you gotta put something there that will make sense, just in case STYLE will not return 0 (zero) 🙂
So I said I’ll be short, so .. till next time.
Can you explain further on this technique? I’m kinda new to this open office thing, and I’d really appreciate it if you can give a step by step guide to doing this. Please and thank you!
1. create new spreadsheet
2. put some data
2.1 in column A, 5 rows: aaa, bbb, ccc, ddd, eee
2.2 in column B, 5 rows representing statuses. For example: 1, 1, 2, 0, 4
3. create 5 styles names Style-0, style-1 and so on (press F11, right click there, new, and bust ahead, giving each a different color)
4. now, assume you want column A to be color coded based on the coded you entered in column B
5. move the data from column A to column C (select all 5 rows and move the selection
6. in column A, row 1, put in formula: =IF(STYLE(“Style-” &ROUND(B1))=0;C1; “error”) then hit ENTER
7. copy-paste or drag-multiply this formula to teh rest of the 4 rows
8. if you’ve done everything correctly, your rows will be colored differently and changing the values in B will change the colors accordingly
Dear Sir,
This was an excellent tip. It worked too well for me also.
I have created plenty of data sheets in calc and have generously applied both logical formulas as well as conditional formatting but was always handicapped by the latter being restricted to a mere three options!
I request you to kindly explain how you created the formula =IF(STYLE(“Style-” &ROUND(B1))=0;C1; “error”) . As I use a lot of logical formulas for auto processing of data, I want to understand this excellent formula of yours and for my personal satisfaction.
Warm regards.
Raja Mukherjee.
The STYLE function has no purpose on returning value, which is why it always returns 0.
You need to use this function in some formula, but in such a way that in the column/cell you want your data to appear, to not alter it.
This is where the IF comes into play.
You put your desired data in some other column (you can hide that one) and then in your desired column you have
=IF(0=0;C1;”irrelevant value as it will never be shown but IF requires it”)
So with above formula, you didn’t change anything in your data. Your spreadsheet still shows the same data (plus the hidden extra helper column)
Now, replacing the 0=0 with STYLE(“Style-0”) will always apply Style-0 to all cells
Next step is to replace that with a condition, formula, function, whatever that maps your condition to a style ID.
like for example
STYLE(“Style-” & IF(C1=1;0;1))
which will use 2 styles, depending on the value in column C
You can have that expression there very complex and cover as many conditional formatting as you like, but I personally don’t like complex expressions.
So instead of that, you would be better of by having a helper column that maps your complex condition to a style “ID” (0…n)
Obviously the style ID doesn’t have to be 0..n
You can simply have in a column textual representation like
success, error, pending, info, etc
And you define your style names as “Style-error”, “Style-pending” and so on
Dear Sir,
Thanks once again.
Kindly advice:-
1) Is it necessary to prefix with “Style” if I name the various styles as “error”, “pending” or whatever? Won’t the formula work if not prefixed by “Style”? If no, then how to write “style”? All caps, all small, any particular rule about that?
2) In these formulas, what does “&” stands for? Is it same as “AND”? Unlikely, because if “&” is replaced by “AND” it does not seem to work.
3) ROUND(B1) in first formula- the formula works without ROUND. So why have you added it?
Thanks and regards,
Raja Mukherjee.
1) absolutely not. You create some styles with some names. Those can be anything. All you need is a way to map to those names based on your condition. How you do that mapping is entirely up to you. For all intent and purposes, you can have your styles named “error”, “warning” etc and have those exact strings loaded from column. So it really doesn’t matter, just be consistent and use an easy convention, whatever suits your purpose. There is no rule on naming styles AFAIK. You can name them JOE and MARRY and it will still work as long as you get those names out of the cells and into the STYLE function.
You can peak in the WIKI also: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_STYLE_function
If you ask about case sensitivity, I don’t know. Test it 🙂 But I gues it’s easier to maintain case, just in case future versions will behave differently.
2) the ampersand is the way to concatenate strings. It’s like the + for numbers.
3) In my case, I had cells with no status value, so B1 would return an empty string. In order for the formula to work, I would have to define the style “Style-“, otherwise the style is not found (I can’t recall if that causes an error or simply does not apply anything). In my case, I had “Style-0” applied to both status 0 and no-satus, hence ROUND which returns a number 0 if the cell is empty. You can just as easily use IF() and default to whatever other style you like if the cell is empty.
Dear Sir,
Thanks once again. That clears this matter.
I have another question but it is not related to this topic. Would you like to help, all the same?
In CALC, if column A contains some auto changeable data, say based on time function like TODAY(), etc, or hourly changing stock prices and I want to record the various data of cell A1 in B1, C1, D1 etc., A2 in B2, C2, D2, etc. and so on, after a defined time interval. then, is it possible to do so without using macros, (as I neither trust macros to be safe nor know anything about it)?
I have searched the net, in vain, but no macroless solution I found!
Thanks again,
Raja Mukherjee.
I need more info on that. A clear example of the cell content over time. Like what’s in all involved cells initially, then after 1 hour, then after another 1 hour and for how long, because the number of columns is finite, so you can run out fo columns…
Dear Sir,
Say in cell A1 the formula is =(B1*C1*D1/100)*(1/365)*(01/01/2015-TODAY()).
Similarly in A2 the formula is =(B2*C2*D2/100)*(1/365)*(01/012015-TODAY())
And so on—-till A100.
Thus hundred rows and 4 columns, A, B, C, D.
Now the value in A columns will change every day as the value of TODAY() will change every day.
Say in column E, F, G— upto 30 columns I need to record the daily data of Columns A1 to A100 for 30 days.
How to record the daily data, automatically in each row of all those thirty columns?
This is a simple example. One can say that why not calculate yourself and paste. In this case it may be possible to do so, But not always, So, is there a process of auto recording of daily data in those thirty columns?
Thanks and regards,
Raja Mukherjee.
I think you hit a dead end here., then you will be able to set the to be the day of the month. You can attempt to search for such a solution.
I know for sure that calc does not allow a formula in on cell to modify another cell. So that approach is out.
I’m not sure, but I wasn’t able to find a way, you also cannot have a cell evaluated based on a condition. It would be nice to tell calc: only evaluate this cell if
You’re 3rd option is to use an external tool that will open and update the cells as needed (write a simple VB script or a program in some language or have someone write it) that will use COM to open the document and copy the required cell value in the target cell for the current day.
And your last option is macros.
Dear Sir,
Thanks for your help. You are also prompt. I shall remember you for any further queries that I may have.
Thanks and regards,
Raja Mukherjee.
Say I want to write a formula in 5
cells of column D (D1 to D5) based on a column which may
vary. In other words, the second column can be B (B1 to B5)
or C (C1 to C5) or any other columns. I don’t know. But i
need the results in column D (D1 to D5). Therefore I make
cells E1 to E5 the input cells. Here I shall mention the
second column from time to time. Now, I want a formula on D1
to D5 in such a way that it picks up the column in question
from E1 to E5.
Say I want to write a logical function on D1;
=IF(“XYZ”E1>0;1;0). “XYZ” is the function I want from
you. If I have written B1 in E1, E1 becomes B1 and it
calculates the value of cell B1.
XYZ = INDIRECT
or, if it’s not absolutely necessary for E1 to contain the column name, then simply use
=IF(E1>0;1;0)
and in E1 you put
=B1
or
=C1
Seriously people, there are specialized forums for these kind of questions.
There is an extension that fixes this now Add as many CF as you want
http://ooo-forums.apache.org/en/forum/viewtopic.php?f=9&t=44591