Absolute cell references in formulas

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Absolute cell references in formulas

Jaron Kuppers
Hello all,

I wanted to discuss selecting cells while writing formulas as it pertains to
absolute cell references (aka $A$1).  It would be nice to see functionality
that allows the user to select the cell as an absolute reference for both
column and row references.  Lets say the user wants the absolute reference
$A$1.  Currently they select the cell and then manually convert it to an
absolute reference.
Instead I suggest something like this:  The user goes to select cell A1, and
holds down the Alt key.  When they select the cell they are prompted with a
dropdown that lets them choose between the 3 absolute reference choices, ie
$A1, A$1, $A$1 which are then inserted into the formula.  The issue is more
complicated than I make it seem because the user can select cells using the
arrow keys as well, and I don't have a solution for that scenario.

Perhaps this functionality already exists, but I could not find it in the
help menu and if it does, it certainly doesn't seem intuitive (I tried a
bunch of key combinations).  There may be issues in the issue tracker but I
didn't have time to look yet and didn't want to forget to post the idea.

Cheers,
Jaron

FYI: As I see this issue being relevant to OOo 3.0, I don't consider this a
uniquely "Renaissance" topic.  If you think otherwise please feel free to
move the thread.
Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Niklas Nebel
On 01/23/09 19:17, Jaron Kuppers wrote:

> I wanted to discuss selecting cells while writing formulas as it pertains to
> absolute cell references (aka $A$1).  It would be nice to see functionality
> that allows the user to select the cell as an absolute reference for both
> column and row references.  Lets say the user wants the absolute reference
> $A$1.  Currently they select the cell and then manually convert it to an
> absolute reference.
> Instead I suggest something like this:  The user goes to select cell A1, and
> holds down the Alt key.  When they select the cell they are prompted with a
> dropdown that lets them choose between the 3 absolute reference choices, ie
> $A1, A$1, $A$1 which are then inserted into the formula.  The issue is more
> complicated than I make it seem because the user can select cells using the
> arrow keys as well, and I don't have a solution for that scenario.
>
> Perhaps this functionality already exists, but I could not find it in the
> help menu and if it does, it certainly doesn't seem intuitive (I tried a
> bunch of key combinations).  There may be issues in the issue tracker but I
> didn't have time to look yet and didn't want to forget to post the idea.

With the cell reference selected (from reference input or otherwise),
press Shift-F4.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
In reply to this post by Jaron Kuppers
Hello Jaron,

Le 23 janv. 09 à 19:17, Jaron Kuppers a écrit :

> I wanted to discuss selecting cells while writing formulas as it  
> pertains to absolute cell references (aka $A$1).  It would be nice  
> to see functionality that allows the user to select the cell as an  
> absolute reference for both column and row references.

+1

Does Numbers and Excel have such a feature?

> Lets say the user wants the absolute reference $A$1.  Currently  
> they select the cell and then manually convert it to an
> absolute reference.

> Instead I suggest something like this:  The user goes to select  
> cell A1, and holds down the Alt key.  When they select the cell  
> they are prompted with a dropdown that lets them choose between the  
> 3 absolute reference choices, ie $A1, A$1, $A$1 which are then  
> inserted into the formula.

Ok, first: why the Alt key? Why not simply a right-click, this is the  
usual way to open a contextual menu… (on mac os, you can do CTRL
+click!) Right-click do not seems to do anything actually.

Note also that two modifier keys are already used: Shift and Command  
(on mac, I don't know the key on PC). Shift extend the selected cell  
or range, and Command adds the cell as a new argument (moving the  
cursor right, inserting a semi-colon, and then the cell reference.)

Finally, we can drag and drop to select a full range. In a full range  
there are 16 kind of references, too much for a menu (and doing a  
drag-and-drop with the right button is not usual.) Ok, all value may  
not make sense, but which ones do we keep?

I already see it: "press Alt and Command and then drag-and-drop from  
A1 to B5, then in the 11th line of the menu select '$A$1:$B5'…"

Another solution would be to use something similar to the Direct  
Manipulation Snippets.

> The issue is more complicated than I make it seem because the user  
> can select cells using the arrow keys as well, and I don't have a  
> solution for that scenario.

On Windows PC keyboard there is a Contextual Menu key :)
Ok, nobody uses it :(

One solution would be to use the '$' key, since it make no sense to  
use it this time. The key could shift between '$A1', 'A$1', '$A$1'  
and 'A1' again…

> Perhaps this functionality already exists, but I could not find it  
> in the help menu and if it does, it certainly doesn't seem  
> intuitive (I tried a bunch of key combinations).

This is the problem with this kind of feature: it is not discoverable…

> There may be issues in the issue tracker but I didn't have time to  
> look yet and didn't want to forget to post the idea.
>
> FYI: As I see this issue being relevant to OOo 3.0, I don't  
> consider this a uniquely "Renaissance" topic.  If you think  
> otherwise please feel free to move the thread.

If we consider that this is a new feature, it is not for Renaissance ;)

Regards,

Clément.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
In reply to this post by Niklas Nebel

Le 23 janv. 09 à 19:24, Niklas Nebel a écrit :

> On 01/23/09 19:17, Jaron Kuppers wrote:
>> I wanted to discuss selecting cells while writing formulas as it  
>> pertains to absolute cell references (aka $A$1).  It would be nice  
>> to see functionality that allows the user to select the cell as an  
>> absolute reference for both column and row references.[...]
>
> With the cell reference selected (from reference input or  
> otherwise), press Shift-F4.

Of course, we should have thought about it, it was so… intuitive :)

Ok, I have more than sarcasms to offer. What if the 'absoluteness' of  
the reference was displayed graphically? I often forget or misplace a  
'$' in a formula, only to discover my mistake after I have copied the  
formula in other cells. Having a visual feedback of the kind of  
reference could help to spot such mistakes…

We could use (e.g.) a wider colored border when that border is fixed  
because of an absolute reference. So:
* 'A1' would have a normal border
* '$A1' would have wider top and bottom borders
* 'A$1' would have wider left and right borders
* all borders of '$A$1' would be wider
* for the range '$A$1:B5', only the top and left borders would be wider.

Alternatively, we could use the length of the border (reaching the  
corners or not), dotted borders for non-absolute references, etc.

Best Regards,

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
In reply to this post by Clément Pillias

Le 23 janv. 09 à 19:48, Clément Pillias a écrit :

> Finally, we can drag and drop to select a full range. In a full  
> range there are 16 kind of references, too much for a menu (and  
> doing a drag-and-drop with the right button is not usual.) Ok, all  
> value may not make sense, but which ones do we keep?

And I forgot that sheets references can also be absolute or relative.  
So we can have up to 32 different different references to a same cell  
range.

Regards,

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Graham Perrin
Administrator
In reply to this post by Niklas Nebel
On 23 Jan 2009, at 18:24, Niklas Nebel wrote:

> holds down the Alt key

<http://www.diigo.com/04olz> for an annotated view of
<http://developer.apple.com/documentation/userexperience/Conceptual/AppleHIGuidelines/XHIGUserInput/chapter_12_section_3.html 
 >

On Mac OS X, alt (option) key is used for basic movement of the  
insertion point so please be prepared to vary the chosen key to avoid  
conflicts.

Best
Graham

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Philip Ganchev
In reply to this post by Clément Pillias
On Fri, Jan 23, 2009 at 2:07 PM, Clément Pillias
<[hidden email]> wrote:
> Le 23 janv. 09 à 19:24, Niklas Nebel a écrit :
>> With the cell reference selected (from reference input or otherwise),
>> press Shift-F4.
...

It would be great if we found a more discoverable mechanism.  By the
way, Alt+drag would not work on Linux and Solaris because it is
usually reserved for moving windows.

> What if the 'absoluteness' of the
> reference was displayed graphically? ...
>
> * 'A1' would have a normal border
> * '$A1' would have wider top and bottom borders
> * 'A$1' would have wider left and right borders
> * all borders of '$A$1' would be wider
> * for the range '$A$1:B5', only the top and left borders would be wider.
>
> Alternatively, we could use the length of the border (reaching the corners
> or not), dotted borders for non-absolute references, etc.

I like these ideas very much, especially the dotted border idea.

On Sat, Jan 24, 2009 at 9:51 AM, Clément Pillias
<[hidden email]> wrote:
> Le 23 janv. 09 à 19:48, Clément Pillias a écrit :
>
>> Finally, we can drag and drop to select a full range. In a full range
>> there are 16 kind of references, too much for a menu (and doing a
>> drag-and-drop with the right button is not usual.) Ok, all value may not
>> make sense, but which ones do we keep?
>
> And I forgot that sheets references can also be absolute or relative. So we
> can have up to 32 different different references to a same cell range.

Relative sheets are conceivable, but probably rarely used. Likewise,
for a range inside a sheet, I guess that the beginning and end are
usually both relative or both absolute. For example common ranges are:
A1:C3, A$1:C$3, $A1:$C3, $A$1:$C$3.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias

Le 26 janv. 09 à 01:41, Philip Ganchev a écrit :

>>> Finally, we can drag and drop to select a full range. In a full  
>>> range there are 16 kind of references, too much for a menu (and  
>>> doing a drag-and-drop with the right button is not usual.) Ok,  
>>> all value may not make sense, but which ones do we keep?
>>
>> And I forgot that sheets references can also be absolute or  
>> relative. So we can have up to 32 different different references  
>> to a same cell range.
>
> Relative sheets are conceivable, but probably rarely used. Likewise,
> for a range inside a sheet, I guess that the beginning and end are
> usually both relative or both absolute. For example common ranges are:
> A1:C3, A$1:C$3, $A1:$C3, $A$1:$C$3.

Actually, the Shift+F4 key combination only cycles thru the 8 cases  
where both extremities of a range have the same absolute or relative  
definitions. To be precise, the order is:
Sheet1.A1:B2
$Sheet1.$A$1:$B$2
$Sheet1.A$1:B$2
$Sheet1.$A1:$B2
$Sheet1.A1:B2
Sheet1.$A$1:$B$2
Sheet1.A$1:B$2
Sheet1.$A1:$B2

Regards

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Christoph Noack
In reply to this post by Clément Pillias
Hi Clément,

I strongly support the idea of making cell references a lot more
discoverable. I have seen many users who worked many years with
Calc/Excel/... and still don't know about absolute/relative cell
references. Nobody did explain it to them, and the company did not spend
money to educate them...

So what about making such little colored cell markers a bit more
contextual?

Example with some 5-second-thoughts (assuming that the user edits a
formula which refers to such a cell/range):
      * If the cell is selected or the mouse pointer hovers the cell
        area, then...
              * show little pins to indicate sticky behavior
              * show other modifiers to e.g. improve the dragging area
              * show e.g. a "close item" to remove the the cell
                reference from the formula
              * ...
      * If the cell is not selected / the mouse pointer is not above the
        cell area, then simplify the visualization.

Something which could be nicely animated with the new drawing layer (if
it is available in Calc).

Bye,
Christoph

Am Freitag, den 23.01.2009, 20:07 +0100 schrieb Clément Pillias:
> We could use (e.g.) a wider colored border when that border is fixed  
> because of an absolute reference. So:
> * 'A1' would have a normal border
> * '$A1' would have wider top and bottom borders
> * 'A$1' would have wider left and right borders
> * all borders of '$A$1' would be wider
> * for the range '$A$1:B5', only the top and left borders would be
> wider.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
Hi Christoph,

Some very quick and incomplete comments because it is getting late.

Le 26 janv. 09 à 22:46, Christoph Noack a écrit :

> I strongly support the idea of making cell references a lot more
> discoverable. I have seen many users who worked many years with
> Calc/Excel/... and still don't know about absolute/relative cell
> references.
> Nobody did explain it to them, and the company did not spend
> money to educate them...

Yes, I even have a reference for an issue asking for that feature :)  
One problem is that making the feature discoverable (e.g. providing  
clickable areas to set absoluteness) does not really help to  
understand how to use it, since it has interest mainly when copying  
the cells, not when the user edit the formula.

> So what about making such little colored cell markers a bit more
> contextual?
>
> Example with some 5-second-thoughts (assuming that the user edits a  
> formula which refers to such a cell/range):

> * If the cell is selected or the mouse pointer hovers the cell
>   area, then...
>      * show little pins to indicate sticky behavior
>      * show other modifiers to e.g. improve the dragging area
>      * show e.g. a "close item" to remove the the cell reference
>        from the formula

The risk is that the user believes that it will "close the window"  
ie. remove the colored border (but not the reference.)

> * If the cell is not selected / the mouse pointer is not above the
>   cell area, then simplify the visualization.

+1 I like this kind of ideas as you know :)

> Something which could be nicely animated with the new drawing layer  
> (if it is available in Calc).

I also have ideas to show references that are currently not visible,  
either because they are out of the visible area of the sheet, on  
another sheet, hidden cells or filtered ones.

More about this tomorrow :)

Good night everybody!

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias

Le 27 janv. 09 à 00:49, Clément Pillias a écrit :

>> * If the cell is selected or the mouse pointer hovers the cell
>>   area, then...
>>      * show little pins to indicate sticky behavior
>>      * show other modifiers to e.g. improve the dragging area
>>      * show e.g. a "close item" to remove the the cell reference
>>        from the formula
>
> The risk is that the user believes that it will "close the window"  
> ie. remove the colored border (but not the reference.)

Oh yes, I forgot: a good thing could be that clicking on the  
highlighted cells selects the corresponding reference in the formula,  
so that keyboard entry can be used to edit it (or delete it!)

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Jaron Kuppers
Hi,

In my absence I see this thread has had a lot of activity!  I am very fond
of Cléments ideas for cell highlighting.



On Mon, Jan 26, 2009 at 6:55 PM, Clément Pillias <[hidden email]>wrote:

>
> Le 27 janv. 09 à 00:49, Clément Pillias a écrit :
>
>>     * show e.g. a "close item" to remove the the cell reference
>>>       from the formula
>>>
>>
> Oh yes, I forgot: a good thing could be that clicking on the highlighted
> cells selects the corresponding reference in the formula, so that keyboard
> entry can be used to edit it (or delete it!)


Could Clément and Christoph please explain in more detail their vision for
cell selection within the formula as you stated above.  I am not sure I
understand exactly what you are describing.  Please remember that a formula
may have more than reference of a single cell and that the multiple
references may be logically independent from each other.  (What I mean is
that someone may only want to remove one reference to A1 not all of the
references to A1 within a formula when using your proposed selection
deletion system).

As far as my previous post is concerned:

Clément wrote:

> Ok, first: why the Alt key? Why not simply a right-click, this is the usual
> way to open a contextual menu… (on mac os, you can do CTRL+click!)
> Right-click do not seems to do anything actually.

No reason.  I just wanted to give an example of what we could do, not
suggest a final solution.  I was interested in hearing what others had to
say about what interface type would be worth considering.  Perhaps the
reason I didn't suggest right-click was that if the user wants to use both
command and the absolute reference they could press two modifier keys, ie
command+control+click gives "$A$1" or some such thing where "command"
controls row and "control" controls column.  However, due to the limitation
in key modifiers (especially with Windows) such a system seems unfeasible.

*Radial menus?*
If right clicking were to invoke an absolute reference menu it would be nice
if it was 2-dimensional, maybe as a radial menu.  For example using ASCII
graphics:

|_____|__A____|____B_____|__C___|
|__1__|_($B$2)_|__(_$B2_)_|_______|
|__2__|__(B$2)_|_*selected*__|_______|
|__3__|_______|__________|_______|

Hopefully it is clear that the text in parentheses are menu options in the
"radial" menu.  This way the user can pick the option intuitevly since a
simple list will not have an intuitive order.  Selected area absolute
references could be handled a few different ways using radial menus as well.

*Problem with absolute selection using menus:*
Whatever the solution we choose, if the interface is invoked and the user
decides to close that interface, the user needs to feel comfortable closing
said menu without selecting another reference.  To clarify: Recall that if I
select A4 and then select A6 without typing text in the formula bar that the
reference in the formula changes;  if a pop-up window is invoked by right
clicking on a cell, the user may feel uncomfortable closing the menu by
clicking somewhere off the menu since they don't know if it will change
their current reference.  Basically, I see the potential for user experience
consistency problems.


Off topic:
In my humble opinion, we spend a lot of time discussing changes for Writer,
when Calc users number almost the same as Writer users.  Some effective and
intuitive UI solutions may cause a user mass exodus from Excel to Calc.  I
plan to further suggest changes to the graphing/chart menus; I believe cell
selection/formula writing and graphing are the two largest turn-offs of
calc.

Cheers,
Jaron
Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Stefan Weigel
Hi,

Jaron Kuppers schrieb:

> Some effective and
> intuitive UI solutions may cause a user mass exodus from Excel to Calc.

This won´t happen by adding additional fancy UI features unless we
don´t fix those countless UX drawbacks from Calc, that already exist
for a long time and offend users day by day.

Only a few examples to be found in issue tracker:

3560, 7357, 8443, 10865, 15522, 17993, 20495, 21869, 32303, 32310,
32344, 34093, 50886, 70015, 70135, 72304, 74662, 74733, 75638,
84899, 85161, 89232, 89970, 89972, 93495, 96338, 97093

This is no judgement about the new ideas that are being discussed
here. Sure some are excellent. But, before spending little available
manpower on further development, shouldn´t we focus on fixing the
hurdles of the current UI?

Stefan

--
www.datenpilot.org

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
Hi Stefan,

Le 27 janv. 09 à 07:48, Stefan Weigel a écrit :

> Jaron Kuppers schrieb:
>
>> Some effective and intuitive UI solutions may cause a user mass  
>> exodus from Excel to Calc.
>
> This won´t happen by adding additional fancy UI features unless we  
> don´t fix those countless UX drawbacks from Calc, that already  
> exist for a long time and offend users day by day.

Some users unfamiliar with spreadsheets or who have only simple usage  
of it may be sensible to "fancy features". Moreover, formula editing  
is THE very basic operation in spreadsheets, so improving it would  
benefit ALL users, and even experimented users could be very sensible  
to it.

> Only a few examples to be found in issue tracker:
>
> 3560, 7357, 8443, 10865, 15522, 17993, 20495, 21869, 32303, 32310,  
> 32344, 34093, 50886, 70015, 70135, 72304, 74662, 74733, 75638,  
> 84899, 85161, 89232, 89970, 89972, 93495, 96338, 97093

Only had a look to a few of these issues, and most of them do not  
require attention from the UX team (despite being tagged with  
"usability") since only some development is needed and no interface  
change.

Some of us (in the UX team) know how to code, but this is may not be  
our first competence. I think that our time is better used in  
imagining new interactions technics. Of course the issues mentioned  
have to be fixed, but this might not be our job, here.

> This is no judgement about the new ideas that are being discussed  
> here. Sure some are excellent. But, before spending little  
> available manpower on further development, shouldn´t we focus on  
> fixing the hurdles of the current UI?

It is hard to know what is important for the user. Is it solving an  
old issue or improving something that nobody asked for? The old  
issue, despite its visibility, might only concern very few people,  
and the new stuff ease the life of everybody, but since nobody  
thought about it, nobody raised an issue. Knowing this kind of stuff  
is our job. Of course we might be wrong, but following a purely issue-
driven development process has proven many time to be the wrong way  
too...

Regards,

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Stefan Weigel
Hi Clément,

Clément Pillias schrieb:

> Only had a look to a few of these issues, and most of them do not
> require attention from the UX team (despite being tagged with
> "usability") since only some development is needed and no interface change.

 From what I understand, the UX team should not only be envolved
when it comes to changes of the interface, but whenever it concerns
"usability, productivity and enjoyment". At least that´s what I read
from the project´s homepage. ;-)

So do you really think, the issues mentioned do not handicap
usability and productivity of OOo and do not require attention from
the UX team?

Of course, UX is not supposed to fix issues by coding. But IMO UX
could judge and propose priorities to the developers. :-)

Cheers,
Stefan

--
www.datenpilot.org

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
Hi Stefan,

Le 27 janv. 09 à 17:44, Stefan Weigel a écrit :

> Clément Pillias schrieb:
>
>> Only had a look to a few of these issues, and most of them do not  
>> require attention from the UX team (despite being tagged with  
>> "usability") since only some development is needed and no  
>> interface change.

Since I said that, I have read ALL these issues, and added myself in  
the CC: list of some issues.

> From what I understand, the UX team should not only be envolved  
> when it comes to changes of the interface, but whenever it concerns  
> "usability, productivity and enjoyment". At least that´s what I  
> read from the project´s homepage. ;-)

;)
Yes, this is certainly something that concerns every contributor :)

> So do you really think, the issues mentioned do not handicap  
> usability and productivity of OOo and do not require attention from  
> the UX team?

Every bug handicap usability and productivity. But most bugs do not  
require User Experience expertise to be solved. If something does not  
work as it is supposed to, and there is no discussion about the "how  
it is supposed to", simply fix it.

Well OK, it is our job to tell the "how it is supposed to" :)

> Of course, UX is not supposed to fix issues by coding. But IMO UX  
> could judge and propose priorities to the developers. :-)

Of course. So I think that none of the issues you mentioned has a  
higher priority than easing the edition of formulas. Well maybe 2 or  
3 of these issues have the same priority (89232 and 70015 among those  
I follow) because of the severity of the issue (but the issue is less  
frequent.)

Now, do not hesitate to signal other issues here, if you think that  
it needs our attention :)

Regards,

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Clément Pillias
In reply to this post by Jaron Kuppers
Hi Jaron,

I don't have much time to answer your mail :(

Le 27 janv. 09 à 03:04, Jaron Kuppers a écrit :

> Could Clément and Christoph please explain in more detail their  
> vision for cell selection within the formula as you stated above.

I will do it here (currently a draft):
http://wiki.services.openoffice.org/wiki/Calc/Cell_Edition_Modes

> I am not sure I understand exactly what you are describing.  Please  
> remember that a formula may have more than reference of a single  
> cell and that the multiple references may be logically independent  
> from each other.  (What I mean is that someone may only want to  
> remove one reference to A1 not all of the references to A1 within a  
> formula when using your proposed selection deletion system).

Yes it may cause issues…

> […]
> *Radial menus?*
> If right clicking were to invoke an absolute reference menu it  
> would be nice if it was 2-dimensional, maybe as a radial menu.  For  
> example using ASCII graphics:
>
> |_____|___A____|____B________|__C____|
> |__1__|_($B$2)_|__(_$B2_)____|_______|
> |__2__|__(B$2)_|_*selected*__|_______|
> |__3__|________|_____________|_______|
>
> Hopefully it is clear that the text in parentheses are menu options  
> in the "radial" menu.  This way the user can pick the option  
> intuitively since a simple list will not have an intuitive order.  
> Selected area absolute references could be handled a few different  
> ways using radial menus as well.

Interesting idea, but as for every non-standard interaction  
technique, it should be tested.

> *Problem with absolute selection using menus:*
> Whatever the solution we choose, if the interface is invoked and  
> the user decides to close that interface, the user needs to feel  
> comfortable closing said menu without selecting another reference.  
> To clarify: Recall that if I select A4 and then select A6 without  
> typing text in the formula bar that the reference in the formula  
> changes;  if a pop-up window is invoked by right clicking on a  
> cell, the user may feel uncomfortable closing the menu by clicking  
> somewhere off the menu since they don't know if it will change
> their current reference.  Basically, I see the potential for user  
> experience consistency problems.

+1, but there are safe places such as the input line of the formula  
bar or the cell content. And we can change the mouse cursor to give  
more feedback.

> Off topic:
> In my humble opinion, we spend a lot of time discussing changes for  
> Writer, when Calc users number almost the same as Writer users.  
> Some effective and intuitive UI solutions may cause a user mass  
> exodus from Excel to Calc.  I plan to further suggest changes to  
> the graphing/chart menus; I believe cell selection/formula writing  
> and graphing are the two largest turn-offs of calc.

I also believe that from a User Experience perspective, Calc is more  
fun ;)

Regards,

Clément.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Christoph Noack
In reply to this post by Clément Pillias
Hi Clément!

Am Dienstag, den 27.01.2009, 00:49 +0100 schrieb Clément Pillias:
> Some very quick and incomplete comments because it is getting late.

When I look at the time you seem to spend on OpenOffice.org topics, it
already seems that you got rid of breathing and eating. Do you really
need to sleep? ;-)

> Le 26 janv. 09 à 22:46, Christoph Noack a écrit :
>
> > I strongly support the idea of making cell references a lot more
> > discoverable. I have seen many users who worked many years with
> > Calc/Excel/... and still don't know about absolute/relative cell
> > references.

> Yes, I even have a reference for an issue asking for that feature :)  
> One problem is that making the feature discoverable (e.g. providing  
> clickable areas to set absoluteness) does not really help to  
> understand how to use it, since it has interest mainly when copying  
> the cells, not when the user edit the formula.

Concerning "being discoverable" I totally agree with you. At least if
there is some indication during editing, they might become used to such
visualization - whether it is required for their work or not. And when
copying/moving cells, why don't we simply re-use these indicators until
the action has been finished.

Example: The user copies a formula which refers to several other cells
(absolute and relative references). During moving the handle for 'copy',
similar indications are made visible like in the edit mode. Those cells
which are pinned (and show that) just move - the pinned ones are somehow
'fixed'. This concept of "simple pins" and showing "what is going on"
might just help the user to better understand the system image.

I think the more challenging part is to provide such feedback in a) the
limited space available (many users seem to attend contests for making
cells as small as possible), and b) avoid creating too much visual
noise.

@Jaron: You already asked some questions how it would be possible to
make such a feature work in every case (e.g. multiple cell references to
the same cell). I still cannot answer you that question, because it is
still a rough idea for me. Maybe we will get to that point :-)

> > So what about making such little colored cell markers a bit more
> > contextual?
> >
> > Example with some 5-second-thoughts (assuming that the user edits a  
> > formula which refers to such a cell/range):
>
> > * If the cell is selected or the mouse pointer hovers the cell
> >   area, then...
> >      * show little pins to indicate sticky behavior
> >      * show other modifiers to e.g. improve the dragging area
> >      * show e.g. a "close item" to remove the the cell reference
> >        from the formula
>
> The risk is that the user believes that it will "close the window"  
> ie. remove the colored border (but not the reference.)
>
> > * If the cell is not selected / the mouse pointer is not above the
> >   cell area, then simplify the visualization.
>
> +1 I like this kind of ideas as you know :)
>
> > Something which could be nicely animated with the new drawing layer  
> > (if it is available in Calc).
>
> I also have ideas to show references that are currently not visible,  
> either because they are out of the visible area of the sheet, on  
> another sheet, hidden cells or filtered ones.

No I must be dreaming... Just click on the reference and OOo will
smoothly slide to the corresponding document section, uncover hidden
cells, ...

Bye,
Christoph


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Absolute cell references in formulas

Philip Ganchev
On Thu, Jan 29, 2009 at 9:16 AM, Christoph Noack
<[hidden email]> wrote:
...
> Am Dienstag, den 27.01.2009, 00:49 +0100 schrieb Clément Pillias:
...
>> Le 26 janv. 09 à 22:46, Christoph Noack a écrit :
...

>> One problem is that making the feature discoverable (e.g. providing
>> clickable areas to set absoluteness) does not really help to
>> understand how to use it, since it has interest mainly when copying
>> the cells, not when the user edit the formula.
>
> Concerning "being discoverable" I totally agree with you. At least if
> there is some indication during editing, they might become used to such
> visualization - whether it is required for their work or not. And when
> copying/moving cells, why don't we simply re-use these indicators until
> the action has been finished.
>
> Example: The user copies a formula which refers to several other cells
> (absolute and relative references). During moving the handle for 'copy',
> similar indications are made visible like in the edit mode. Those cells
> which are pinned (and show that) just move - the pinned ones are somehow
> 'fixed'. This concept of "simple pins" and showing "what is going on"
> might just help the user to better understand the system image.

This would be totally awesome. And I agree it would also suggest how
to use references it in formulas. Maybe after pasting a cell that
contains a formula that refers to other cells, highlight the
references until the user moves the cursor. Or maybe even whenever the
cell cursor is over the cell.

> I think the more challenging part is to provide such feedback in a) the
> limited space available (many users seem to attend contests for making
> cells as small as possible), and b) avoid creating too much visual
> noise.
>
> @Jaron: You already asked some questions how it would be possible to
> make such a feature work in every case (e.g. multiple cell references to
> the same cell). I still cannot answer you that question, because it is
> still a rough idea for me. Maybe we will get to that point :-)

a. Show references only if there is enough space
b. Maybe like [1] but that only works for pasting single cells. If you
are moving several cells, it can get too cluttered.

[1] http://wiki.services.openoffice.org/wiki/Image:OOo_Calc_cell_references_proposal.png

>> > ... assuming that the user edits a
>> > formula which refers to such a cell/range):
>>
>> > * If the cell is selected or the mouse pointer hovers the cell
>> >   area, then...
>> >      * show little pins to indicate sticky behavior
>> >      * show other modifiers to e.g. improve the dragging area
>> >      * show e.g. a "close item" to remove the the cell reference
>> >        from the formula
...
Sounds good; the last one is probably not needed.

>> I also have ideas to show references that are currently not visible,
>> either because they are out of the visible area of the sheet, on
>> another sheet, hidden cells or filtered ones.
>
> No I must be dreaming... Just click on the reference and OOo will
> smoothly slide to the corresponding document section, uncover hidden
> cells, ...

You mean click on the text "SomeOtherSheet:A1"? That could work.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]