Created attachment 120528 [details] sample 4 interrupted forrmula sequence I'm working in an enterprise with about 600 users. Calc is heavily used with row or column based formulas like: A 1 =a1*1,19 2 =a2*1,19 3 =a3*1,19 4 =a4*1,19 5 =a5*1,19 ... 1024 =a1024*1,19 1025 =a1025*1,19 ... and so on I often see formula mistakes by users, who accidently interrupt those formula sequences: A 1 =a1*1,19 2 =a2*1,19 3 =a3*1,19 4 =a3*1,19 ooops, a3 reference comes accidently twice! 5 =a4*1,19 ... 1024 =a1023*1,19 1025 =a1024*1,19 ... and so on Currently, there is no warning, tool tip or highligting for formula sequence interruptions. A warning - eg. by a little danger sign with tool tip help next to the affected cell - would be a nice to have feature for future releases.
Created attachment 120529 [details] how it could be
Created attachment 120530 [details] Sample stable formula and conditional format verification Hi Heiko, I think you can solve the issue avoiding the wrong formula. I don't know what version you are using, from LibreOffice 5 there is the option to use entire column references https://wiki.documentfoundation.org/ReleaseNotes/5.0/es#A:A_.2F_1:1_entire_column.2Frow_references A formula like: =INDEX(A:A;ROW();0)*1,19 or =INDEX($A:$A;ROW();0)*1,19 always produce the right result, use the absolute reference as you need. With previous versions define the rows numbers: =INDEX(A$1:A$1000;ROW();0)*1,19 or =INDEX($A$1:$A$5000;ROW();0)*1,19 It can be done similar with OFFSET() or INDIRECT(), but they are volatile functions, more expensive on calculation time, specially if they are a lot of them. Also you can define a Named formula with it so you can use directly their name. Find attached a sample file. This can be done also with Labels but actually there is a problem with their calculation after copy, so better avoid it. Another way to mark it could be with a conditional format
Seems like a perfectly valid request. Setting to NEW.
All the human intelligence in a formula checker? I doubt that works. How about other sequences like 1,3,5... or 1,2,4,8... or 1,3,2,4...? What about 1.19*a2? And on the other hand it's not a big deal to compare the reference with the row number. Unless you have a striking idea how this checker could be implemented (some kind of pseudo code would be nice) I'd say the simple use case can be done via extension but the generic solution is WF. Feel free to reopen.
Sequences like =A1*1.19 =A2*1.19 ... *can* be detected to be identical if they use the same relative references. Actually that already happens to create shared formulas, i.e. such sequences are not stored in memory as individual formulas repeated over and over again but only once and a block belonging together. A checker could inspect whether such blocks are neighbouring and identical but interrupted with a row (or up to n rows) of something else. Not as an extension though because the internal model is needed.
(In reply to Eike Rathke from comment #5) > Sequences like > ... > *can* be detected to be identical if they use the same relative references. Very limited scope. Feel free to reopen if this is tempting anyway. Happy to propose a UI solution then.