Description: I've had some big spreadsheets over the years. The further you get away from A1 the more complex and confusing 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 relative to a temporary naming relative to A1 being the upper-left-most cell, and proceed normally throughout the area. 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. To access the real A1 or B3 or other cell, use a syntax like !A1+!B3 or some such. Internally, it's still recorded as AA281 amnd AB283, but for presentation, because it's relative to that A1 window, 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. Actual Results: This is an enhancement. The outline above is just guidance. It can be expanded / altered as needed to align with existing standards and practices. Just the general concept of a relative-to-A1 addressing is the real feature. Expected Results: This is an enhancement. Not sure why I have to fill out this field. :-) Reproducible: Always User Profile Reset: No Additional Info: It's a nice feature to makes large spreadsheets easier to work with.
ohoho....It looks strange and very hard for real work UX-team, what do you think?
BTW something can be done with Named Ranges using relative address in it. But I can't believe even the U team spending time on this.
If you want to enter =AA281+AB283 the easy way is per clicking the cells. In case you have more advanced it's possible to use the INDIRECT() function, eg. BA1==AA, BA2==281, BB1==AB, BB2==283 -> =INDIRECT(BA1&BA2)-INDIRECT(BB1&BB2). While the use case is clear I doubt the relative area is a good solution. It wouldn't be clear what cells are calculated precisely since you may always have so relation. There might be solutions but I cannot imagine anything absolute clear for every user. So my take: WF.
Another option that might be simpler and just occurred to me could be a relative view from the current visible page and visible cells. Click the "Relative View" icon, and the upper-left cell becomes A1. Things proceed across and down from there, but only on the visible page. As you scroll around, the upper-left cell becomes A1 in the new location. Turn it off, back to normal. Cells could be displayed like this in Relative view: [ ][ A1.AA281 ][ A2.AB283] ... The whole purpose of this feature would be to allow easy human-readable / writable references for quick-and-dirty calculations wherever you are in the sheet, so you can populate a value, range of values, whatever it is, without having to use the fully qualified multi-letter + lengthy number references. When you complete your work using the more convenient names, turn Relative View off, and now the formulas are auto-updated with their more lengthy / less convenient names. Usage: Toggle Relative View on/off. Off is like it is today. On always makes what you see in the upper-left cell be A1, proceeding over and down from there to something like O39 if that's the lower-right cell. The Relative View is always just the visible cells on-screen in this design / use scenario. It wouldn't work with any type of programmatic automation.
Should be: Cells could be displayed like this in Relative view: [ ][ A=AA ][ B=AB] ... [ 1=281 ] [ 2=282 ] [ 3=283 ] Something like that. Not sure the = is the best character, but it could also just display like this, with hovering tooltips showing the real values: [ Rel ][ A ][ B ] ... [ 1 ] [ 2 ] [ 3 ] Other people can figure out the details. The whole concept is to make it easier to use.
Taking this whole "Relative A1" concept a step further, it's almost like the idea of having virtual spreadsheets within a root spreadsheet. Imagine you're on cell K92, and you need a calculation to feed into that cell's value. Rather than consume 2D space in the surrounding row,col cells, you dive into 3D and create a virtual spreadsheet at that cell which can pop up and handle normal input. That virtual spreadsheet pops up with a full A1..max_extent sheet available with all of the abilities the parent had. And from there you could have more pop up, all relative to the parent. Rather than having new sheet tabs to select from, you can reference the calculations directly there, at the cell, in the virtual sheet. Ctrl+PgUp enters the virtual sheet. It pops up to a 1/3rd screen presentation by default looking like a popup, and you can assign values to the "RESULT" name to populate a value into that cell using something like: RESULT=[normal formulas / values here]. Only one RESULT cell can be used for the virtual. If you need to update more information, reference it by a long-hand syntax identifying the tab and cell. There's so much possibility here. It's time to evolve a little and think "flexibility!" (be sure you read that with the same intonation as Phlox saying, "Optimism, Captain!")
Actually the only thing I can think of that would make this simple and not blow up in complexity is to support a relative referencing from the current cell you edit the formula in, which will immediately change. Something like <L1U1> (left 1 up 1) <R2D4> (right 2 down 4). If the cursor is in cell C13 this would be replaced immediately with B12 and E17. BTW. implementing Table functionality from Excel would solve some of the issues as with Tables you can reference with the named column or row and with auto-completion this gets easy.
If I'm not wrong table functionalities works in Calc, but you need to save the file in xlsx format. https://wiki.documentfoundation.org/ReleaseNotes/5.1#Table_structured_references Similar, in some way, to table functionalities is the use of Labels in ODF, (Menu/Sheet/Named ranges/Labels) Also there is the option to use ExcelR1C1 formula syntax. Something like <L1U1> (left 1 up 1) = =R[-1]C[-1]
We could introduce the option "A1 always on top" and have negative values like -C -B A +B +C -3 -2 -1 +1 x +2 (you never see negative values when the cursor moves, only with scrolling) But I'm still rather for WF.
As said above, either use named expressions with relative cell references, or go to menu Tools -> Options -> Calc -> Formula, Formula Options, Formula syntax, select Excel R1C1 syntax => problem solved. We will not introduce yet another reference syntax. Period.
This is an open source project? It's not a reference syntax. It's a display and input feature. The underlying syntax remains the same. The only difference is what's seen on screen in relative mode, and how things are parsed when in relative mode. The values in the upper-left refer to A1 and proceed out and down from there, but internally everything remains the existing syntax. That's the beauty of this enhancement. Nothing changes except the input parser and the display, and only when in relative mode, and only for the visible cells. Even the visible cells maintain their original designated identity entirely as by however they're designed today internally.
It would be yet another different reference syntax in the context of the compiler lexicalizer and tokenizer/parser (and stringizer). Also clashes between what now is a valid named expression like L1U1 and then suddenly couldn't be anymore would have to be resolved. Certainly more to come.. Regarding "this is an open source project?", yes of course, provide a fully working patch (preferably along with unit tests) and we could happily integrate it. I personally won't invest my time on developing this. Specifically I also don't see the need as the Excel R1C1 syntax does exactly that, just with the slight inconvenience of having to type in the additional [] brackets for the relative addressing.
"...provide a fully working patch (preferably along with unit tests) and we could happily integrate it" Point me in the general direction in the source code base and I'll begin investigating it. I'll put together a proposed change outline for review, and if it's acceptable I'll code it. Sound doable?
I was thinking this morning that 7 things would need to be modified to make this work, and 8 if you include unit testing: 1) An icon to toggle Relative View. 2) A keyboard shortcut to toggle Relative View, and to turn it on or off explicitly. 3) A post-processing step on UI presentation, where the actual formula for the cell is processed to reference !!native cells (like !!A1 for the real A1), and to take anything in the visible range and PRESENT IT as a relative name. 4) A pre-processing step on the syntax parser for formula input, that does the reverse of 3), making the relative values be real values. 5) A new flag in the saved ods file indicating if the sheet's in relative view or not. 6) An alteration to the row and column painting algorithms to paint them in relative view mode. 7) Changes to the help file. 8) Unit testing. Nothing in the core algorithms, parsers, or logic would have to change. This could be implemented as a visible-at-the-UI-level change only, with post- and pre- processing code injected in 3) and 4) as indicated.
(In reply to Rick C. Hodgin from comment #13) > Point me in the general direction in the source code base and I'll begin > investigating it. Of specific interest are include/formula/grammar.hxx formula::FormulaGrammar::AddressConvention formula/source/core/api/grammar.cxx sc/inc/address.hxx ScAddress::Details sc/source/core/tool/address.cxx specifically the parts that handle a ScAddress::Details sc/source/core/tool/compiler.cxx to see how things are called in ScCompiler::IsSingleReference() ScCompiler::IsDoubleReference() ScCompiler::CreateStringFromSingleRef() ScCompiler::CreateStringFromDoubleRef() and maybe formula/source/core/api/FormulaCompiler.cxx for the base class implementation.
(In reply to Rick C. Hodgin from comment #14) > 3) A post-processing step on UI presentation, where the actual formula for > the cell is processed to reference !!native cells (like !!A1 for the real > A1), and to take anything in the visible range and PRESENT IT as a relative > name. You mean you want to re-parse the formula string generated from tokens and present it differently? Sounds error-prone to me. > 4) A pre-processing step on the syntax parser for formula input, that does > the reverse of 3), making the relative values be real values. Same here, pre-processing a string to produce another string is an error-prone overhead. > 5) A new flag in the saved ods file indicating if the sheet's in relative > view or not. I'd not save/load that to/from file. It would be utterly confusing to a user loading such document if not familiar with the feature/syntax. > This could be implemented as a visible-at-the-UI-level change only, with > post- and pre- processing code injected in 3) and 4) as indicated. You'd have to re-implement a lexicalizer just to handle the cell reference bits. But maybe I'm missing something in your idea. Anyway, if that's the approach to go then probably post-processing should take place in sc/source/ui/view/tabvwsha.cxx ScTabViewShell::UpdateInputHandler() line 669 where rCell.mpFormula->GetFormula(aString) is called. The pre-processing probably in sc/source/ui/view/viewfunc.cxx ScViewFunc::EnterData() that detects a formula entry and ScViewFunc::EnterMatrix().