[Solved] Changing Color of n Fields

[Kapa150]

Hi Everybody,

I'm relatively new to using OpenOfficeCalc and i can't figure out how to solve an issue i have.

I made a Spreadsheet that calculates the Coordinates for objects arranged on a circle. so if you give it a center point, a radius and a number of objects (n) it will calculate the coords automatically. (i use that for a game, see attached file: Circleology V2)

because people who don't know how the spreadsheet works will use it, i want the relevant fields to change color, so everybody knows which coordinates to use. in the spreadsheet i have something like 60+ lines, but usually only a smaller n is needed.

so for example if i picked n=10 then i want the first 10 results of x and y to be a certain color.

i hope you can help me with this, thanks!

[Lupp]

Welcome to the forum!

I have some problems to understand.

Mainly: In what way do you "pick" the value of n.

Generally you can ovelay attributes like the background colour to cells conditionally using the tool
>Format>Conditional Format...
Any combination of attributes you want to overlay (over the original attributes of the cell / its cell style) you need to define a named CellStyle for.

Editing: See attached.

aoo101690Circleology_V2_reworked.ods

(29.48 KiB) Downloaded 143 times

[Kapa150]

by that i just meant that it's the value the user picks. it's a variable, that changes. and i want the color of the x and the y coordinates of n fields to change with it, so the user sees which coordinates to copy.
thx for the quick response

[Lupp]

The word "pick" isn't unambiguous enough. So is "variable" in the context. Spreadsheets get values entered into cells and calculated results are returned to cells. If somebody "picked" a value this way, tell what cell he used (or is expected to use).
If you want to use any tool probably named a "picker" like the annoying "Date Pickkers" e.g, also tell.

(See the attachment to my previois post.)

[Kapa150]

the users know what cell to use, that's not the issue. the issue, as explained, is that i don't know how to conditionally change the colour of the cells.
i looked at your attachment and i don't quite understand what you did. i want the color of the x and the y coordinates to change (so the A and B column), not the line.
do you know what the formula for conditional formating would be if i wanted to colour n number of lines using the counters (in column H or I)?
essentially i want it to do this: if (number in H1) is bigger than 0, then change the colour of A1 and B1 to (blue (or whatever)). and then do that for the entire colum?

thanks again for the quick response and the patience

[Lupp]

As I already told you, you should use ConditionalFormatting. That's everything I can tell you regarding your "...please limit your responses to that." If you insist on telling me how my answers should look or what I am allowed to ask in return, we won't get anywhere.

Studying the formula used in the CF dialog of my example you should be able to adapt it to your needs. If not, you may need to ask not for an answer or a hint, but for direct and specific assistance - and if somebody is ready to serve you then, this would surely require complete and precise information about what you eventually want to achieve.

So far there is a serious lack of clarity. Using the "counters in column H or I" is not an option since there are many pairs of numbers, and conditional formatting of a depending number of rows only can be done for a specific number - or a pair if two columns should be affected independently. How would your "knowing" user "pick" the needed informatiom?

[Kapa150]

the "please limit your response to that" sounded more rude than i intended it. i removed it from the comment.

"Using the "counters in column H or I" is not an option since there are many pairs of numbers, and conditional formatting of a depending number of rows only can be done for a specific number" that's a shame

i guess i will look up how the CF thing works, maybe i ll find something. ty for the responses

LG aus Österreich

[Zizi64]

i want the color of the x and the y coordinates to change (so the A and B column), not the line.

Please give us more details: Which lines of the x, y values or which values you want to colorize - depending on what other values or what state of another cels?

Please upload YOUR sample file - colorized manually, and with some information about the actual state.

[Kapa150]

sure @zizi64

i attached two files that should illustrate what i'm looking to do. (one for n=10 and therefore 10 colored fields in cells A and B and one for n=15 and 15 colored cells in A and B)

i hope this clears things up

[Zizi64]

It seems as a pure CF problem. I just named the cell D3 with name "Count", and I used it in the condition. (First I selected the filled-in range in the columns A, B.)

Circleology Zizi64.ods

(31.07 KiB) Downloaded 142 times

The sample file was edited in my LibreOffice 6.2.8

[Kapa150]

thanks so much zizi!!! it works
it only marks 9 and not 10 (but that's easy enough to fix)

[Zizi64]

it only marks 9 and not 10 (but that's easy enough to fix)

Yes, i see now... You must use an offset value for the function ROW() in the condition formula.

[Kapa150]

"Yes, i see now... You must use an offset value for the function ROW() in the condition formula."
i don't know what that means xD
here is how i solved it: ROW()<=Count+1 (i just added the +1 to your solution)

so your initial response solved everything for me! that was exactly what i was looking for!
many thanks!

[Lupp]

"... You must use an offset value ... in the condition formula."

Yeees...

But that's no sorcery. You surely want to understand better.
The function ROW() doesn't return a number of rows but the number (index) of a specific row for which it was called.
Generally it is used with a cell reference as its paramter. If this parameter is omitted it works for te cell for/from which it was called.

To get a number of rows you can use the function ROWS() which needs to be called with a reference to a CellRange.
You see: Zizi64 shortened the formula by implying that the range for which he wanted to count the number of rows started a row number 1. In fact it started at number 2 what caused the flaw. With other words: The offset you are talking of depends on the the row-number of the first row needing to be taken in account.

For the next step you need to understand the way absolute and (vs.) relativ addressing works. Do you? If not read the tutorial viewtopic.php?f=75&t=39529 about "Ten concepts that every Calc user should know".

Looking at the sheet again enter the address of the range you want to define a ConditionalFormat for into the NameBox (left of the FormulaBar). No need to be stingy. Lets think of A2:B1001. CF of the discussed kind isn't critiocal concerning efficiency. (If you insist on selecting the range by dragging the mouse, you should start at the bottom-right corner. This makes understanding easier in AOO.)

Now open the CF dialog. Thinking it from the one cell having the focus-borders (on the example A2 now) you should enter the formula

Code: Select all

ROWS(A$2:A2) <= 10 if you want 10 rows highlighted or
ROWS(A$2:A2) <= Count if Count is again used to pass the number or
ROWS(A$2:A2) <= $Z$2 If cell Z2 shall be asked for the number.

No matter which one of these formulas you use: The ROWS() expression will adapt the relative parts of its RangeAddress to whatever cell for which it needs to be evaluated when the decision is due whether to highlight it or not. If this is cell B7, e.g. the actual call to the ROWS() function will use the range B$2:B7 and therefore return 6 as the number of rows...
Got it?

Don't worry if it looks slightly complicated. You will soon get along with this kind of considerations - if you seriously try. And if you think you shouldn't, spreadsheets beyond a bit of adding expenses aren't an option for you.

[Zizi64]

here is how i solved it: ROW()<=Count+1 (i just added the +1 to your solution)

THAT IS an offset (additional) value!

[Lupp]

THAT IS an offset (additional) value!

Assuming you mean it literally: One of the rare cases I would contradict you.
I regard the "+1" named an offset by "Kapa150" a misleading way to write

Code: Select all

+ Row(A$2)-1

which would, of course be better placed on the left side of the comparison as a

Code: Select all

- (Row(A$2)-1))

BTW: I still think

Code: Select all

ROWS(A$2:B2)

is the clearest and thus best way to pass the needed number to the condition.

The relevant difference will get obvious as soon as you insert an additional row above current row 1.

See attachment demonstrating the difference - and pleading for the usage of "semantically correct" expressions in place of surrogates only working as needed under restrictions.

aoo101690conditionalHighlightingOfVariableNumberOfRows_1.ods

(55.17 KiB) Downloaded 138 times

[Kapa150]

THAT IS an offset (additional) value!

oh
and thx again, for being the only one who was helpful

take care zizi

[Zizi64]

for being the only one who was helpful

I have a differing (contrary) opinion about this...

[Lupp]

Why doesn't occur a proper joke to me now?
@Zizi64: Please feel sure of my high regard.