I have been keeping my stock portfolio in LibreOffice (OpenOffice) for over 10 years. Until a week ago, I used LibreOffice 3.3.4, now that is 5.2.7 and in the upgrade process, I’ve lost a “feature”. In my spreadsheet The individual stock make the rows, days make the columns. The cells hold the stock price. Columns a-f have some information. Column G is today. H is yesterday, I the day before, etc. to the beginning of the year, currently in column DK. each cell has conditional formatting that makes the background color green if today’s price is higher (or equal) than yesterday’s and red if it is lower. For the past 10 years I have added new data as follows: I insert a new column at H, copy the G column over to H, then fill out today’s stock price in G. That keeps the G column anchored as that is referenced in various graphs. New data is added between the first column (G) and last, so graph references always span the entire data set. I have never had to adjust the conditional formatting. Cells in the G-column would always reference H and cells in the H-column would reference I. But now the conditional formatting formulas in the G column reference values in the I-column, after I insert a new column at H (just like other formulas would do, after an insert) and after copying the G-column data to H, the H-cells reference data in the J-column. It doesn’t seem to matter whether I insert a column to the right of G or to the left of H. Is there a way to have things work the way they did in the older 3.3.4 version or was this once considered a “bug” that was “fixed” for good?
My conditions are simple of course: e.g. for cell G2:
(1) Cell value is - less than H2 - Apply style “down”;
(2) Cell value is - greater than or equal to H2 - Apply style “up”
There were changes to CF when the CF manager was introduced. (There are some issues I know of.)
If you do not need newly implemented features of LibreOffice you may “sidegrade” to Apache OpenOffice V4.1.5 where the CF was not changed as far as I can see. I don’t know the details you are considering …
If you want to stick to LibO you can replace the conditions (for “green” e.g.) this way:
-0- Select the range for CF, initially G:H.
-1- Select the mode ‘Formula is’
-2- Enter the formula G1>OFFSET(G1;0;1)
-3- Do respectively for additional conditions.
-4- OK.
-5- I would also suggest to not Paste Ctrl+V the copy of G into H, but to only Paste Special... Shift+Ctrl+V the values.
(This may avoid unwanted effects to the CF.)
All excellent suggestions. Thanks for taking the time to understand the issue and suggest a solution. I have implemented the conditional formatting now with the offset(ref,row,col,height,width) function and that (of course) solves the issue. No hard-coded reference to the neighboring column, just “1 column to the right”. That works like a dream.
You may “accept” the answer by clicking the checkmark left of it.
After LibreOffice-5 crashed 5 times on me, trying to recreate a chart (reverse x-axis; logarithmic y-axis; 18 years of portfolio data), I gave up on LibreOffice-5 and following the other suggestion, sidegraded to OpenOffice-4.1.5 Sorry guys, I can’t use crashing software. Having been a young software developer myself, I know working in a “structure” can be hard. I seems though that some bad quality DNA has crept into this project. Good luck, and thanks for the ride.