Forum Discussion
Charts do not accept ranges with hash #
I suppose it is something. Even there I would tend to take control of my own solution with
Name: MyValidationList
Refers to: = MyTable[MyColumn]
List
Source: MyValidationList
It is simply that I detest the spreadsheet practice of direct cell referencing in its entirety!
The again, I was never happy to see a core dump that also uses direct memory addressing.
In many cases it looks like overcomplication. range=A1:A10 and SUM(range) is more complex than SUM(A1:A10)
It is a further level of abstraction but potentially the name 'range' could be chosen to inform the use as to the business significance of the content. The direct reference tells one the location of the content. Unlike practically every other spreadsheet user, I do not have the slightest interest in knowing the location, provided I can return the values it holds. If necessary, the Name box allows me to go to the range location, still without invoking any direct cell referencing.
I also avoid the practice of relative referencing (the concept that a formula might return different results depending on where one happens to write it is a frankly weird aspect of the spreadsheet paradigm), so that leaves me with $A$1:$A$10. which, in my book is a pretty unappealing notation.
I could well understand a traditional spreadsheet user asking, "So why are you still here? Wouldn't you be better off with a programming language in which arrays are identified by the pointer, offset and dimension?"
I am so happy with modern Excel just because I can indulge my preferences (some might say prejudices) and Lambda helper functions, as well as the passing of Lambdas as function arguments, take the possibilities beyond anything I had envisioned.
Aside. I have a vague memory of reading:
"There is no IT problem that cannot be solved by a further layer of abstraction, except, too many levels of abstraction".
- Good points. To add
Despite being the most useful software for the vast majority of us, Excel has a bad reputation for being deceptively unreliable.
A1:A50 kind of referencing system is one of the problems that causes this. Unless I updated all the formula/ charts/ pivots/ lookups, I might still be using A1:A50 data while the range expanded.
I am a huge fan of Lambda (and the eco systems, such as the new dynamic functions and ranges). With Advanced Formula Environment, Lambda is probably the biggest innovation in Excel. It allows one to think in terms of data transformation and results instead of how one will achieve it. A lot of sheets with intermediate steps are no longer needed. The formula are very expressive and maintainable.
But some components of excel (such as the charts) still look aged and need to update catch up. I will love to simply provide a lambda to the chart instead of providing these ranges on the sheet.We seem to be in agreement with one another, if not the rest of the planet!
I don't think the chart engine SERIES accepts formulae, full stop.
I defined
f = LAMBDA(t, t ^ 2) where (sheet scoped) x = f(s!y) y = SEQUENCE(2*n+1, 1, n,-1) =SERIES(s!$F$3,s!x,s!y,1) works fine as a scatter chart.To write the values as a dynamic range on tab "s" one has
= HSTACK( x, y )The other appallingly dated functionality is Conditional Formatting. I would wish to specify the applies to range by name (dynamic names being marginally more important than normal defined names) and want the condition to be defined as an array formula (I have little use for cell-by-cell evaluation).
