Use LUA with conditional formatting

English
,
#1

Is it possible to use LUA code for conditional formatting?

My project is that I have a table with many values and a field where people should enter their measurements. Because of human errors (and a lack of interest) the values are often wrong. Right now I use conditional formatting to counter this, but
a) It is like ~ 200 entries
b) it can be broken rather easy by just ‘using’ the file (copy and paste can be enough for that)
It also is a huuuuge hassle to fix the ~200 entries once they are broken, because all of them are broken at the same time with one copy&paste action.

It would make my life much easier if I could just collect the values in a table in LUA code and just combine LUA and conditional formatting.

#3

Sorry, why not use ‘Data validity’?, it is designed for that.

#4

To be honest, I have never used data validity and have no idea how it works (which wouldn’t be a huge problem for long I guess).

The option (be it data validity or something else) has to be able to do a couple of things:

  • Check in real time if a value entered by a user is within a specific boundry (pressing any “calcucalte” button will not work)
  • The value to be checked is different depending on the rest of the table content (eg. if A1 is “foo” the value has to be something different that if A1 is “bar”)
  • The thing must not be overwritten by a user doing ordinary work in the table (eg. entering values, copy&paste, etc.)
  • This has to work on not only one single cell, but the same operation has to work on ~2’000 cells at the same time

Can data validity do something like this?

EDIT: After a quick test this does not seem like it would work how I need it to work,

#5

As long as you don’t give any information about this secret, no one can answer you.

#6

It has to work like this:
Cell A1: User enters one of ~100 values
Cell B1: User enters a float number
Cell C1: User enters a float number

The float number in cell B1 and C1 have to be in a set boundary that changes depending on the value in A1. If the values in B1 and C1 match they are coloured green, if not they are coloured red.

Same for values entered from A2, B2 and C2 to A1500, B1500, C1500. B and C always have to be checked in real time depending on the user input and give immediate feedback weather the value is right (within the boundary) or wrong (outside the boundary).

#7

You must know that Data Validity is ignored if the cell content is pasted.

Data Validity in B1: AND(VLOOKUP($A1;$J$1:$L$3;2;0)<=B1;VLOOKUP($A1;$J$1:$L$3;3;0)>=B1)

Data Validity in C1: AND(VLOOKUP($A1;$J$1:$L$3;2;0)<=C1;VLOOKUP($A1;$J$1:$L$3;3;0)>=C1)

imagen
CondForm_Sierra1.ods (9.5 KB)

#2

IMHO a double no: Conditional formatting uses no macros, and I never seen any “internal” macros written in lua. (I’m aware there is a Lua-uno bridge to send commands to Open/LibreOffice, but I don’t see a way to use this for the usual conditional formatting.
.
But, as you may get access to the sheet, your macro could do the formatting itself. You could also create a tag in a second sheet (same dimensions) “behind” your data by macro, wich can then be used by conditional formatting.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.