New Feature Request: A1 Relative Cells

[RickCHodgin]

I've had some big spreadsheets over the years. The further you get away from A1 the more complex it is to input formulas and keep track of things.

I've had the idea for relative cells. You mark off a rectangle by selecting it, then right-click, and a menu option says "View Relative."

In this mode, whatever cells are in the selected area become A1 in the upper-left, and proceed normally throughout. This allows people to work with smaller numbers even when they're out in AA218 and thereabouts. Rather than typing =AA281+AB283, they can use relative cells for input as =A1+B3. Much simpler on the eye.

Underneath, it's still recorded as AA281 amnd AB283, but for presentation, because it's relative, we see it in that easier range.

It would be undone by clicking the X in the upper-right area. It could also be dragged around so the relative area moves into other cells. The sides could be expanded or collapsed as necessary to resize. A hotkey could be setup to provide a standard 12 x 20 grid or whatever common size would be nice. Once the relative grid is there, it can be dragged as needed.

Just seems like a nice way to view complex data when it's out there in the spreadsheet sticks.

[RusselB]

Welcome to the Forums.
If you would like to see this added, follow this link to make it an enhancement suggestion.

[RickCHodgin]

Welcome to the Forums.
If you would like to see this added, follow this link to make it an enhancement suggestion.

Thank you.

Bug #127633 -- Enhancement

[Alex1]

Instead of typing AA281 you can click on the cell while entering the formula.

[keme]

The greatest drawback I can see with the suggested enhancement is that outside references would be difficult. With a range acting as a "virtual sheet", how would you differentiate between references to "virtual A1" and the actual A1 cell of the sheet?

You already recognized the issue, and suggested the exclamation point "... or some such".
Specifically, the exclamation point is already in use as a range operator (range intersection) in Calc, and also as a cell address separator in other spreadsheet software. In math it is of course the factorial, and in logic formulas it is often used as the "not" operator.

Using some other punctuation character will also most likely only add to confusion instead of resolving it, even for cell addresses such as IO1010 and ALZ70854. Simplifying is a nice idea, but I suspect that it may complicate things just as often as it will simplify.

There is already an alternative reference syntax available in LibreOffice Calc (and Excel): The RnCn notation. While this is less compact than "A1 notation" in most cases, it is often easier to read.

[RickCHodgin]

I was thinking, the ability to have A1 relative cells doesn't have to be in a block.

A user could click the icon, and then the screen switches to this layout no matter where they are in the sheet:

Code: Select all

[ Rel ][ A ][ B ][ C ][ D ] ...
[  1  ]
[  2  ]
[  3  ]
[  4  ]
   ...

Click it again and it toggles back to real row/col numbers and names.

The "Rel" text in the upper-left square would indicate visually you're in relative mode. Everywhere you scroll through the sheet in that case leaves the upper-left cell reference as A1, essentially giving you a screen that's always in that simplest formula / form order of starting at A1.

For sheet references, use !!A1 then. A "not not" which means "the real A1."

Developers are smart. They can figure out the best solution for implementation. The idea is just to have a consistently easy way to do input no matter where you are on the sheet. For actual data input, it's not a big deal. Tabs and the enter key and you're good to go. But for calculations, it would be far easier to have A1-relative references no matter where you were for quick calculations to then put that value into a cell, or to create some ad hoc formulas, and then have the calc software itself convert them to the AK281 and AL283 references.

Human beings deal better with certain things. This solution seeks to address that fact.

[Villeroy]

In LibreOffice call menu:Tools>Options>Calc>Formula
Formula syntax: Excel R1C1
https://tduhameau.wordpress.com/2012/09 ... nce-style/
After adjusting yourself to that syntax, you will never again have any problems with the meaning of absolute/relative references. It is the better formula syntax, very much underrated because of this popular game: https://en.wikipedia.org/wiki/Battleship_(game)

[RickCHodgin]

In LibreOffice call menu:Tools>Options>Calc>Formula
Formula syntax: Excel R1C1
https://tduhameau.wordpress.com/2012/09 ... nce-style/
...

R1C1's not the same for people's thinking. It's powerful, but a very clunky syntax. It's good for automation. It's not naturally intuitive for people's minds, plus there's so much extra typing it's worse than AK281 and AJ283. There's a reason why it's not widely known to users 37+ years after it was invented (by Microsoft for MultiPlan).

Relative A1 addresses a real need for ease of input in a wide range of scenarios. It would be easy to implement (a UI-only presentation of real data underneath, and only when relative mode is on). If it displayed simply the current screen's content for cells, and was fully disabled during any automation, it wouldn't be too difficult to implement.

[Zizi64]

For sheet references, use !!A1 then. A "not not" which means "the real A1."

...and how many relative A1 cell do yo want to create on a single sheet or in a single file? And all of them will be named !!A1 ???

[RickCHodgin]

For sheet references, use !!A1 then. A "not not" which means "the real A1."

...and how many relative A1 cell do yo want to create on a single sheet or in a single file? An all of them will be named !!A1 ???

The syntax !!A1 would reference the real A1. All local references in relative mode would use A1..whatever the extent is for the visible screen.

I had originally wanted to create a "scratch pad" work area. You block off a random group of whatever (8 wide by 15 high cells for example), right-click and choose "Relative View." That gives you a little scratch pad relative to A1 in the upper-left of that marked off block. You can perform some quick-and-dirty calculations, and then deposit the result where you need. If you un-mark the relative cell block, the things you input as A1..H15 would remain there in their properly named/numbered cells, with proper formulas going where they should. It was just for the input, for the reference, for the quick use, that it would be there as a little A1..H15 window in the middle of an otherwise wherever-it-happens-to-be sheet.

But in re-thinking the complexities of that scratch pad area, I though why not make the whole visible spreadsheet relative? Whatever you're looking at, however many cells there are on the screen, make them all be "Relative A1 cells" from the upper-left? But this would only be true when Relative Mode is enabled. The rest of the time it's like normal.

I'm thinking an icon and a hot-key. An "R" in colors like Superman (just kidding). And the hot key would be Ctrl+Alt+R to toggle, for example.

I still like the scratch pad idea. I suppose if I ever write an office suite with a spreadsheet app, I'll add that ability. LOL

If you think about it, this kind of Relative A1 concept is really like a virtual spreadsheet within a spreadsheet. It could be carried forward with that concept. You're in a cell like K92, and you can insert a virtual spreadsheet there, with a return value posted to "RESULT" which comes through to the actual cell.

Lots of concepts there are possible. It just requires thinking outside the box a little.

[RumonC]

In LibreOffice call menu:Tools>Options>Calc>Formula
Formula syntax: Excel R1C1
https://tduhameau.wordpress.com/2012/09 ... nce-style/skassuoc
After adjusting yourself to that syntax, you will never again have any problems with the meaning of absolute/relative references. It is the better formula syntax, very much underrated because of this popular game: https://en.wikipedia.org/wiki/Battleship_(game)

Hi, thanks for your very useful updates. i had a similar issue, now solved with the tricks you provided.
By the way, i also love playing Battleship Game in many versions.