[Solved]Conditional Formatting. . . again.

[Wheelz]

Hi,

I have a grid of numbers and wish to apply conditional formatting on the basis of randomly selected numbers.
The best way to describe this is like the old game of "Bingo" or like the "Lottery".
As random numbers are called, matched numbers in the grid are formatted.

A sample of this can be downloaded here: https://docs.google.com/leaf?id=0B0R-M5gmnxVZODM1NGMzZTItZjRkMi00Nzk5LTgyYmMtNTRiODQyN2RmYmFi&hl=en

I've made several attempts and the only one that seems to work is selecting the whole grid, open "Conditional Formatting", cell value - is equal to - one of the random cells. (Conditions 2 and 3)

Only problem with this is there are only 3 conditions available, unlike MS Word where you can keep adding more conditions.

Selecting the range of cells with random numbers only applies it to the first random cell. (Condition 1)

Any suggestions for this novice?

Thanks,
Pete

[Villeroy]

Condition 1:Formula Is ISNUMBER(MATCH(E11;$E$8:$J$8;0))
E11 beeing the currently active cell. If you selected E11:J20 with another active cell, use that one as relative referemce.

For the records:
If you wanted to use 6 different styles:
STYLE(INDEX(style_list;MATCH(E11;$E$8:$J$8;0)))
with style_list being some vertical list of style names.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Wheelz]

Thank you Villeroy for your reply.

I'm not sure I understand your explanation for Condition 1.

Using your formula, I tried setting it as: ISNUMBER(MATCH(E11:J20;$E$8:$J$8;0)) to get the whole grid to interogate E8:J8 but this didn't work. This is really what I'm trying to achieve so that when the values of E8:J8 are entered they will activate the specified formatting in the grid.
Hope I'm explaining myself a little more clearly.

Thanks,
Pete

[Villeroy]

Please, use my formula as advised and tested.
Understand relative referencing. It applies exactly as in cell formulas.

All you type into the conditions is evaluated as formula expression (with range names and "quoted" literals btw).

Simple demo right on the sheet:
- Copy my
ISNUMBER(MATCH(E11;$E$8:$J$8;0))
into the clipboard.
- Select L11:Q20 besides your original range and make the first cell L11 the current input cell (hit tab if the input cell is Q20).
- Type = and paste my formula behind.
- Hit Alt+Enter in order to enter the active cell's formula into all the highlighted cells at once.

The formulas in the first column
=ISNUMBER(MATCH( E11 ...
=ISNUMBER(MATCH( E12 ...
=ISNUMBER(MATCH( E13 ...
=ISNUMBER(MATCH( E14 ...

Likewise in the first row:
=ISNUMBER(MATCH( E11 ...
=ISNUMBER(MATCH( F11 ...
=ISNUMBER(MATCH( G11 ...
=ISNUMBER(MATCH( H11 ...

Exactly the same takes place in the conditional formatting and Data>Validity as well, btw.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Wheelz]

Thank you Villeroy.

Exactly as you said "Understand relative referencing".
That was my mistake, I didn't see how specifying one cell could apply to all in the range.

Works great.

Thanks again.