Lets say you have some data to "a1:a10". a11:a20, a21:a30, etc. You would like to run "index, match" combinations on these (separate) ranges to B columns . This require that the ranges will be locked eg. index(a$1:a$10, ....). There is no quick way to unlock them as you move them with mouse, then to lock them automatically to these new (separate) ranges. steps: 1) ctrl+x (cut) with some additional key to unlock ranges (eg. +u) 2) ctrl+v (paste) with some additional key (if needed) to lock them again. It may be the same key eg. (+u). This will expand usability, and user friendliness of calc. Version: 24.2.0.3 (X86_64) / LibreOffice Community Build ID: 420(Build:3) CPU threads: 24; OS: Linux 6.7; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Debian package version: 4:24.2.0-1 Calc: threaded
I find it hard to understand the use case, sorry. Are you able to share a sample ODS with steps? What exactly do you mean by "lock"?
Created attachment 194791 [details] picture of calc data here you can see what i means in the picture. How i can apply "IFERROR(INDEX($C$6:$D$11, SMALL(IF(D6=1,ROW($A$1:$A$10)), ROW(A1))),"")" for data B and C with a single step? cells "$C$6:$D$11" are locked inside the formula. If I cut-paste the formula below for Data B it will not do it. I must set again all cells. However, if cells "$C$6:$D$11" were unlocked temporarily with eg. cntrl+x and then cntrl+u, then paste it below, it will do it without any other editing (except D6 to D15 in "IF"! It will be a HUGE addon for libre calc! of course when paste it below, cells must be locked again in their new position. eg. for DATA B: IFERROR(INDEX($C$15:$D$20, SMALL(IF(D15=1,ROW($A$1:$A$10)), ROW(A1))),"")
cntrl+u = unlock
Right, so you want to quickly change cell references from absolute to relative and back to absolute. - How would that command know which part of your formula needs to be made relative and which one needs to be made absolute? - Why not keep the reference as C15:D20 in the original formula so it updates when copy-pasted? - Have you used F4 to toggle references between absolute and relative quickly in formulas?
I didnt know about F4. This did what I asked. Thanks. Yeah i know what you mean: what ranges to be unlocked. This is somewhat problematic. when pressing F4, a new popup windows appears and you write in it which ranges to be unlocked eg. 1,2 (1st,2nd) cause 3rd and 4th you are not like to unlock them. Also, the window may shows by default all possible ranges to be unlocked and you select which ones you want to unlock. However, F4 behavior must change. Cause now you must lock them again. In this new way, when unlocking happens, the formula is automatically copied and locked automatically again (the parts that you defined to be unlocked), so you do not need to lock it again. eg. IFERROR(INDEX(C$2:D$11, SMALL(IF(D$2:D$11=1,ROW(A$1:A$10)), ROW(A1))),"") Note that if F4 is intented for different purpose, a new key combination must be found for that.
[Automated Action] NeedInfo-To-Unconfirmed
That sounds to me like way too much effort and over-engineering, for something that can already be solved by using F4 in the pasted formula. I vote -1 but let's see what the UX/Design team thinks.
Sheet > Cycle Cell Reference Type assigned to F4 cycles between col/row, col, row, none. =A1 -> =$A$1 -> =A$1 -> =$A1 -> =A1... Works pretty well and tinkering something so "IF(D6=1,ROW($A$1:$A$10), "...") toggles only D6 sounds inflexible and very special. What you can do is to put the single references into extra cells, something like M1: =D6 and M2: ROW($A$1:$A$10) and use "IF(M1=1, M2, "..."). => WF/NAB (for "F4 behavior must change" or to introduce some other mode)
ok, please close the issue. thanks