Not thrilled by this hidden easter egg. There are plenty of way to fill a range and making this simple convenience function complex is IMO not self-explanatory.

(In reply to Heiko Tietze from comment #1)
> Not thrilled by this hidden easter egg. There are plenty of way to fill a
> range and making this simple convenience function complex is IMO not
> self-explanatory.

Are there ways other than those I have already described that permit a complete fill down? When I mentioned 500 rows it was just flippantly indicating a nominal increase on 50.

As you're no doubt aware, many of the fill functions are crippled by hidden columns and rows where it seems to be quite a "hit & miss" affair with almost random circumstances dictating whether the fill will fill or just leave some cells blank.

Many of us will have sheets of much greater proportions and I certainly have a number with 75,000+ rows - consistency would be beneficial for all users.

(In reply to Colin from comment #2)
> Are there ways other than those I have already described that permit a
> complete fill down?

Personally I would copy, move (left/right), go to the end (ctrl+down), move (left/right), select upwards (shift+ctrl+up), and paste. You can also insert a temporary column where autofill could work. Or use Sheet > Fill Cells > Fill Series on a selection.

(In reply to Heiko Tietze from comment #3)
> (In reply to Colin from comment #2)
> > Are there ways other than those I have already described that permit a
> > complete fill down?
> 
> Personally I would copy, move (left/right), go to the end (ctrl+down), move
> (left/right), select upwards (shift+ctrl+up), and paste. You can also insert
> a temporary column where autofill could work. Or use Sheet > Fill Cells >
> Fill Series on a selection.

I think you may have inadvertently justified my suggestion

Which exactly is the proposal?

Having some new configurable setting?

Adding some by-default shortcut?

Changing some currently-used shortcut?

Adding the desired action to the Customize > Keyboard > Function (list) but without setting a shortcut by default? (I would "vote" for this, if this alternative would be available/possible in said dialogue.)


FWIW, [CTRL]+DOUBLE_CLICK on the bottom-right corner is already used for filling (based on the left-side column, without modifying the initial value).

Generic statement (not limited to this particular case): Please be aware of "randomly" setting by-default shortcuts that are used differently in other LO modules or even in alternative spreadsheet tools. Such seemingly-intuitive shortcuts will trigger difficulties for users of other modules (who assume the shortcuts work "consistently" within LO) or for users that are already used to the same shortcut with a different effect in other spreadsheet tools.

Now, for the example presented in comment 0, there are several alternatives (which takes longer to describe in steps than it takes to completely execute them in practice), such as (one of many possibilities, similar to comment 3):

0. From cell B1, [CTRL]+[C]
1. [RIGHT] (go to C1)
2. [CTRL]+[DOWN]
3. [LEFT]
4. [CTRL]+[SHIFT]+[UP] (or move the scroll bar up and [SHIFT]+click on cell B1)
5. [CTRL]+[V]

Another alternative:

1. Fill in column D
2. Re-arrange the columns as needed (there are several ways to do this, some more efficient than others.

I am very much aware that shortcuts can improve efficiency. I wished there would be shortcuts in many cases for Calc usage that have been available in other spreadsheet tools for decades (making them far more efficient).

The question is whether adding a specific by-default shortcut for this case is wise.

(In reply to ady from comment #7)
> 
> FWIW, [CTRL]+DOUBLE_CLICK on the bottom-right corner is already used for
> filling (based on the left-side column, without modifying the initial value).
>
My initial suggestion is for the ability to differentiate the limiting column which by default is the left column - not the content that is being filled.

It would be nice if the user could choose whether it is the left or right column which in the absence of anything acting as a physical stop in the centre column acts as the limiter at the bottom of its range.

The right column may be filled to the last row and therefore whatever is copied from the centre column would be repeated to the last row.

The question of what is actually copied - the formulaic contents of the cell being double-clicked or the unchanged contents of that cell is, I believe, another issue.

> 
> Now, for the example presented in comment 0, there are several alternatives

I think the most elegant alternative for selecting an array is probably to just type its coordinates into the address box so the array is fully selected for the copy in hand - which involves first ascertaining the limits of the copy. This is worth the extra effort if a source array say 8x8 is to be copied and replicated 100 times but I am initially considering a single formula in a single cell at the top of a column that I would like to replicate to the lowest row defined by either its left or right neighbour

> 2. Re-arrange the columns as needed (there are several ways to do this, some
> more efficient than others.
>
This could be considered for creating an empty column by insertion to the right of the limiting right neighbour column and then editing its top cell with the formula, replicating with a double click and then moving the resulting selected cells into the originally desired location - but it's still much more effort then having the option to "double-click" copy to the extent of a chosen L or R adjacent column.
 
> I am very much aware that shortcuts can improve efficiency. I wished there
> would be shortcuts in many cases for Calc usage that have been available in
> other spreadsheet tools for decades (making them far more efficient).
>
I couldn't agree more
 
> The question is whether adding a specific by-default shortcut for this case
> is wise.

Perhaps the procedure could be to detect that a column fill copy is actioned and then having a simple pop-up asking whether it should be delimited left or right. I believe the ability to select a column of text through the middle of a Writer page "right click drag select" as per Word (now abandoned) was never a feature of Writer anyway.

(In reply to Colin from comment #8)
> My initial suggestion is for the ability to differentiate the limiting
> column which by default is the left column - not the content that is being
> filled.

My point: [CTRL]+DOUBLE_CLICK on the bottom-right corner is _not_ a "freely-available" shortcut; its already in use for a very common task.

While I understand the general intention, the exact proposal is yet not too clearly defined to me. FWIW, I already mentioned my current opinion in comment 7.

 
> Perhaps the procedure could be to detect that a column fill copy is actioned
> and then having a simple pop-up

Disruption for using a shortcut that is initially intended to speed up a commonly-used procedure?

OT: We currently don't have very common keyboard shortcuts when using the Function Wizard; those would be worth implementing. I am not sure that auto-filling based on different areas (other than the left-side column) is _that_ frequent, hence my "vote" in comment 7.

(In reply to ady from comment #9)
> (In reply to Colin from comment #8)

My original suggestion was perhaps [Shft]+Double-Click which wouldn't involve
> 
> Disruption for using a shortcut that is initially intended to speed up a
> commonly-used procedure?

but that appears to have been misunderstood, lost in translation or dismissed.

I do appreciate there are numerous shortcuts and modified shortcuts which perhaps needs further review to identify those being used in real life. Which prompts a related question - Is there a list of all the supported LO shortcuts?

(In reply to Heiko Tietze from comment #3)
>  go to the end (ctrl+down)

Actually, going "down" is not so easy. If you go "down" within the empty column, you don't get the the highest-index row with any content in the sheet, you get to row 1048576.

> Personally I would copy, move (left/right), go to the end (ctrl+down), move (left/right), select upwards (shift+ctrl+up)

1. That doesn't work if you want to autofill based on a selection of multiple cells.
2. That's a rather contrived sequence of actions. And even if a user thought of something like that, they would be deterred by the select-upwards selecting the (last) copied cell.
3. I would rather not need to have to view the bottom of the sheet in order to effect this.

(In reply to Colin from comment #0)
> it will only ever fill-down to row 50

I was actually surprised to find out that if, in your example, you switch the order of the columns, i.e. column A has 500 rows and column C has 50, then autofill in column B will fill down to 500 rather than 50. i.e. the fact that the end of column C is _not_ an obstruction is IMHO a bug. Perhaps I should file it separately?

I should also note that in RTL, autofill currently fills to the end of the column to the right, i.e. it only looks for obstruction in the preceding column (whether you're in RTL or LTR), never the succeeding column.

----

My bottom line: I would like to be able to open a "context menu" for auto-filling for choices regarding:

* Choice of rule for autofill content (copy last element, repeat sequence, continue sequence linearly, etc.)
* Choice of rule for stopping the autofill (i.e. what constitutes an 'obstruction')

Also, I would change the default to consider obstructions on both sides.

(In reply to ady from comment #7)
> FWIW, [CTRL]+DOUBLE_CLICK on the bottom-right corner is already used for
> filling (based on the left-side column, without modifying the initial value).

So this is a 'bikeshedding' point IMHO, but I would go for a right-click on the corner-square handle. Right now, that behaves like a regular right-click on a cell.

> 0. From cell B1, [CTRL]+[C]
> 1. [RIGHT] (go to C1)
> 2. [CTRL]+[DOWN]
> 3. [LEFT]
> 4. [CTRL]+[SHIFT]+[UP] (or move the scroll bar up and [SHIFT]+click on cell
> B1)
> 5. [CTRL]+[V]

Lots of acrobatics. I'm lazy and want to be coddled :-)


> Another alternative:
> 
> 1. Fill in column D
> 2. Re-arrange the columns as needed (there are several ways to do this, some
> more efficient than others.

Requiring an auxiliary column is clunky, and remember - column D may be filled with other things, so we would need to insert a column, then work with it, then delete the column; and if we have objects that are not anchored the right way, they may be hiding column D (like a chart maybe?) ... so, not good enough.

> The question is whether adding a specific by-default shortcut for this case
> is wise.

That's not the main question... the functionality is useful, whether there's a keyboard shortcut for it or not. (And I don't even care much for a keyboard shortcut for this.)

Just in case someone happens to expect some additional feedback from me to prior comments... At this point I see too many misunderstandings in this ticket, and a too-diverse level of experience and knowledge about spreadsheets in general and Calc in particular, which complicates the discussion. To avoid repeating explanations and verbosity, I'll refrain myself from further replies regarding this subject.

(In reply to ady from comment #13)
> Just in case someone happens to expect some additional feedback from me to
> prior comments... At this point I see too many misunderstandings in this
> ticket, and a too-diverse level of experience and knowledge about
> spreadsheets in general and Calc in particular, which complicates the
> discussion. To avoid repeating explanations and verbosity, I'll refrain
> myself from further replies regarding this subject.

Me too, and I filed the enhancement request. I feel that if it's to go further then the experts will look at the functionality and either propose a properly formulated feature or decide it's unworthy of their attention.

(In reply to Eyal Rozenberg from comment #11)
> I should also note that in RTL, autofill currently fills to the end of the
> column to the right...
So "left" is actually depending on LTR/RTL... ("start" is not an option in this case).

> My bottom line: I would like to be able to open a "context menu" for
> auto-filling for choices regarding:
-1, the function is supposed to be super-fast, and context menus aren't. The discussion here is whether we need some modifier key to use the alternative side.

>If you go "down" within the empty column...
The point of move left/right first is to meet the column to go down to the end. Ady and me proposed this workflow.

We have ascertained that LTR/RTL defines which column is the "control column" for the double-click copy down.

If there is a decision to proceed with a modification, perhaps the natural or logical choice for a modifier would be the [Alt] key - indicating that the ALTernate "control" column is to be used.

I have conducted a simple experiment and it appears that [Alt] double-click just performs the same function as an unmodified double-click. That is to say, it replicated the formula to the extent of the "control" column, including stopping at an "obstructed" cell - probably implying the [Alt] key is simply ignored.

The request has to be more clear and precise. It cannot be in one comment to use [CTRL], later to use [SHIFT], and then to use [ALT], or to keep changing the scenarios. This is why I mentioned that "by-default" shortcuts have to be thought slightly deeper than whatever pops up first.

I think that a more precise request could be (mentioned as one of those almost random thoughts in a prior comment):

* Instead of only considering the "left-side" column (or "prior" column or however it would be called for RTL/CTL cases), consider both immediately-adjacent columns and execute Autofill according to the "longest" of the two columns when double-clicking the Autofill corner.

* The Autofill action will remain the same; the difference is that both-sides adjacent columns are considered instead of the "prior" column only. This would be consistent for RTL layout.

* To be determined: what happens when hidden columns are adjacent, at either or both sides of the relevant column.

* No new shortcuts.


For future independent evaluation (i.e. another report): Since Calc lacks an "Autofill icon" next to the autofill corner (available in other spreadsheet tools), new alternative shortcuts+mouse_clicks could show the Fill Series (or a Fill Options if you want) dialogue; or add an equivalent "Autofill icon" to the Autofill corner (as Excel has).

The topic was on the agenda of the design meeting but didn't receive further comments.

Ady summarizes the request in comment 17. Let's add the opportunity to autofill on the other side per alt+click, change the behavior depending on LTR/RTL, and rename the feature (at least in the documentation). This part might be worth an extra ticket but could be solved on the fly.

Code pointer: ScViewFunc::FillCrossDblClick()

(In reply to Heiko Tietze from comment #18)
> The topic was on the agenda of the design meeting but didn't receive further
> comments.
> 
> Ady summarizes the request in comment 17. Let's add the opportunity to
> autofill on the other side per alt+click, change the behavior depending on
> LTR/RTL, and rename the feature (at least in the documentation). This part
> might be worth an extra ticket but could be solved on the fly.

Thanks to you and Ady for progressing it. I use autofilling quite extensively so if I can do anything to help then don't hesitate to suggest it.

(In reply to Heiko Tietze from comment #18)

> Ady summarizes the request in comment 17. Let's add the opportunity to
> autofill on the other side per alt+click,

Just to be clear, I did not suggest to use alt+click. Quote from comment 17 "No new shortcuts". I suggested to take both adjacent sides in consideration when double-clicking on the autofill corner (instead of considering the left-side column only).

(In reply to ady from comment #21)
> (In reply to Heiko Tietze from comment #18)
> 
> > Ady summarizes the request in comment 17. Let's add the opportunity to
> > autofill on the other side per alt+click,
> 
> Just to be clear, I did not suggest to use alt+click. Quote from comment 17
> "No new shortcuts". I suggested to take both adjacent sides in consideration
> when double-clicking on the autofill corner (instead of considering the
> left-side column only).

The suggestion to signify the priority of the Alt(ernate) column with the identified keyboard/mouse combination was indeed mine. ;).

(In reply to Heiko Tietze from comment #19)
> Code pointer: ScViewFunc::FillCrossDblClick()

Which is in sc/source/ui/view/viewfun2.cxx
Note that the current implementation looks for (preferred) data left of the start of the selection, or if not found looks for data at right (apparently no one noticed, because everyone tested with existing data to the left?). That probably could be changed to skip the left lookup and force the right lookup.

Question remains what should happen if there is no data to the right; as Alt+DoubleClick explicitly requests fill aligned to right probably nothing.

(In reply to Eike Rathke from comment #23)
> (In reply to Heiko Tietze from comment #19)
> > Code pointer: ScViewFunc::FillCrossDblClick()
> 
>  skip the left lookup and force the right
> lookup.

Absolutely not. The intention was never to exclude anything that already exists. It's to allow the user a modicum of control when data exists in both columns.
Excluding the left column from the equation removes both control and functionality.
If, as you observed, the system selects the right column if no data exists in the left column then that's already half the logic resolved
> 
> Question remains what should happen if there is no data to the right; as
> Alt+DoubleClick explicitly requests fill aligned to right probably nothing.

If there is no right column - and I'm assuming you're implying there is no left column - then of course nothing can be done.

If there is no right column but there is a left column then the user will notice the lack of compliance annnnnd - Doh, I really do want the left column, let's do it the normal way and just double click.

I concur with one of the earlier comments - "what happens with hidden columns adjacent to the target column" This is already the topic of an existing report where there is inconsistency between copying across hidden columns v copying down through hidden rows" and of course the situation whare an array encompassing both hidden columns and rows is drag filled.
Sorry, I searched but cannot find the report number. I know it exists because I filed it.

Another question is what to do with the manual autofill by mouse dragging the handle. For consistency I suggest to also test whether Alt is pressed and switch prior/after respectively.

Are we just considering the effect upon a single column or multiple selected columns?
It's probably fair to say that most users will be replicating formulae and will expect the formula to either adjust its reference cells or use an "absolute content" depending upon the fill type.
Auto-Filtered rows and "hidden" columns and rows introduce another dimension - should the fill affect or ignore the "hidden" cells?
Most filters are set to "hide" certain data elements for cosmetic or focal objectives.
If there is an array with hidden columns and/or rows then I imagine if a formula is contained in a hidden cell affecting the results of other cells on that row the intention is probably to update all formulae - regardless of their visibility.
simple proof;
enter 1 into A1
enter =A1+1 into B1
Drag-fill B1 to six adjacent columns (G)
Select and Group Columns D&E (F12)
Hide columns D&E
Enter =A1+1 into cell A2
Drag-Fill A2 to G2
Select and Group Rows 5&6 (F12)
Hide rows 5&6
Select A1:G1
Select A2:G2 and drag-fill to row 8
Unhide columns and rows
Now consider the reasons which may have dictated filtering rows with auto filters or hiding columns because the user didn't want to see a piece of text contained in Column H or wanted to hide calculated values higher than 5 or 6 in column C or even because a user broke down a complex formula with D&E being filled with intermediate "helper" calculations for the results in G
Also, remember the objective of filtered rows where the user wishes to change just text content from "in abeyance" to "delivered" by filtering on any number of columns to target the specific rows then changes the top row's text entry and double-clicks, EXPECTING only the targeted rows to change - NOT EVERYTHING between the two extremeties. not shouting - highlighting.

Of course, the user should be aware of the current need to expose "hidden" columns and rows when making "global" formula changes but many will be unaware of the potential hazards or the inherent forgiveness of the undo button.

(In reply to Colin from comment #24)
> (In reply to Eike Rathke from comment #23)
> > (In reply to Heiko Tietze from comment #19)
> > > Code pointer: ScViewFunc::FillCrossDblClick()
> > 
> >  skip the left lookup and force the right
> > lookup.
> 
> Absolutely not. The intention was never to exclude anything that already
> exists.
Seems you misunderstood or I didn't express clearly: in the case of Alt+DoubleClick change the existing flow to skip the left lookup, not change the existing flow for DoubleClick without Alt.

(In reply to Eike Rathke from comment #28)
> (In reply to Colin from comment #24)
> > (In reply to Eike Rathke from comment #23)
> > > (In reply to Heiko Tietze from comment #19)
> > > > Code pointer: ScViewFunc::FillCrossDblClick()
> > > 

> Seems you misunderstood or I didn't express clearly:

A bit of both really. I assumed you were referring to existing procedures as the proposal for Alt+double click is not yet an implemented feature and wasn't mentioned until later.
I now realise you were expanding upon the proposal:)

(In reply to Colin from comment #26)
> Are we just considering the effect upon a single column or multiple selected
> columns?
> It's probably fair to say that most users will be replicating formulae and
> will expect the formula to either adjust its reference cells or use an
> "absolute content" depending upon the fill type.
> Auto-Filtered rows and "hidden" columns and rows introduce another dimension
> - should the fill affect or ignore the "hidden" cells?
> Most filters are set to "hide" certain data elements for cosmetic or focal
> objectives.
> If there is an array with hidden columns and/or rows then I imagine if a
> formula is contained in a hidden cell affecting the results of other cells
> on that row the intention is probably to update all formulae - regardless of
> their visibility.
> simple proof;
> enter 1 into A1
> enter =A1+1 into B1
> Drag-fill B1 to six adjacent columns (G)
> Select and Group Columns D&E (F12)
> Hide columns D&E
> Enter =A1+1 into cell A2
> Drag-Fill A2 to G2
> Select and Group Rows 5&6 (F12)
> Hide rows 5&6


> XXXXXXXX   Select A1:G1 -  XXXXXX OOPS, IGNORE THIS IT'S A CUT N'PASTE ERROR


> Select A2:G2 and drag-fill to row 8
> Unhide columns and rows
> Now consider the reasons which may have dictated filtering rows with auto
> filters or hiding columns because the user didn't want to see a piece of
> text contained in Column H or wanted to hide calculated values higher than 5
> or 6 in column C or even because a user broke down a complex formula with
> D&E being filled with intermediate "helper" calculations for the results in G
> Also, remember the objective of filtered rows where the user wishes to
> change just text content from "in abeyance" to "delivered" by filtering on
> any number of columns to target the specific rows then changes the top row's
> text entry and double-clicks, EXPECTING only the targeted rows to change -
> NOT EVERYTHING between the two extremeties. not shouting - highlighting.

We are once again suggesting behaviors and shortcuts without careful consideration.

Using different shortcuts or obtaining different behaviors in different tools just makes it more difficult to (most) users, and the more-specific shortcuts and behaviors are only used by a minority of users, who must be specifically aware of them. Minor difference in behavior and users get annoyed and confused. The use of F4 in Calc comes to mind (as a very simple case), but not only that one.

Considering Eike's comment 23:
> Note that the current implementation looks for (preferred) data left of the
> start of the selection, or if not found looks for data at right (apparently
> no one noticed, because everyone tested with existing data to the left?).

It is a hint that users might not really need _shortcuts_ for different areas to be considered for AutoFill, and that having data on the left-side column is the most common situation. Moreover, the right-side column is already being considered (according to the same quote), just not with the algorithm that Colin would want for his needs.

Parallel situation: VLOOKUP (index must be first column) vs. alternative Calc functions.

There should also be an additional consideration: clicking or grabbing by mistake just the AutoFill handle instead of the inside of the cell, while pressing some additional key (alt, ctrl, shift, whatever); the result would not be the original intention. Mispositioning the mouse pointer is more common than you might think, especially for users with accessibility problems.

IIRC, MS Excel takes the longest of the adjacent columns when double-clicking the AutoFill corner handle. Unfortunately, I don't have access to MS Excel ATM for me to corroborate this.

Since Calc is already considering both adjacent columns (see comment 23), my suggestion would be to also take the longest of the two as priority (with same considerations as the current ones, such as finding an empty cell), instead of adding shortcuts just to modify the precedence. Same as Excel (IIRC).

From users' perspective, the algorithm is simple to understand and to use. RTL layouts would behave exactly the same. And in case the desired AutoFill expansion would be for the shortest column, deleting ("extra") data after the initial AutoFill is simple enough with already-known shortcuts.

Ideally, Calc should allow for keyboard+mouse shortcuts to be configurable just as keyboard-only shortcuts, but that would be a different request report.

My original thought was for something as simple and obvious as Alt+double-click to prioritise the right column instead of the left column. The assorted "exceptions" and "pitfalls" now make this feel more like trying to fix something that isn't broken - with great potential for breaking it in the process.

Personally, I would regularly use the ability to choose the priority column but then again I've "always" double clicked allowing it to reach it's own limit and if I needed greater depth then I'd ctrl+down to the end of that column, assess and adjust the terminal point and just double-click again - or cut and paste to a preselected row limit.

Is it time for me to withdraw the enhancement request?

(In reply to Colin from comment #32)
> My original thought was for something as simple and obvious as
> Alt+double-click to prioritise the right column instead of the left column.

That would be "simple and obvious" because you specifically thought about it and have the need. For a user that wouldn't know about this or don't have the need or is used to a different experienced behavior in other spreadsheet tools would not think of it (i.e. it is not really "obvious"). For users with accessibility problems, it is not "simple" either.

KISS (other than maintaining the current algorithm): AutoFill corner handle extension (double-click) would consider the "longest" column to _both_ sides (with same current caveats); no new shortcuts.

Note: It could be helpful (see comment 31) if someone with MS Excel (and/or other spreadsheet tools) could check the current behavior with at least 3 columns and report it here.

(In reply to Colin from comment #35)
> (In reply to Colin from comment #34)
> > (In reply to ady from comment #33)
> > > (In reply to Colin from comment #32)
> > > > My original thought was for something as simple and obvious as
> > > > Alt+double-click to prioritise the right column instead of the left column.
> > > 
> > 
> > > 
> > > Note: It could be helpful (see comment 31) if someone with MS Excel (and/or
> > > other spreadsheet tools) could check the current behavior with at least 3
> > > columns and report it here.
> > 
> > Excel
> > 1        1
> > 1        1
> > 1        1
> >          1
> >          1
> > 
> >          1
> >          1  Excel fills to here ignoring both empty cells ie to the lowest
> > occupied row
> 
> I put a formula in the middle (blank) column

Sorry, more haste less speed
When column A has 1 blank and then continues to the same level as column C so they both have a blank cell it fills to the lowest extremity. If column A terminates as above it fills to the break in column C. ie right priority.

That seems logical. Excel finds the lowest extremity of the columns but will terminate at the point - if any - where gaps coincide thereby creating the end of the "array"