Forum Discussion
Charts do not accept ranges with hash #
I am not sure I am entirely comfortable with that. It is undoubtedly clever to infer what the user would like (provided that is indeed what the user wanted) but that does not remove the need for Excel to accept correctly formulated dynamic array notation.
That's what we have. We can't use MyTable[MyColumn] in data validation list, conditional formatting, etc. but automatically expansion together with table and spill referenced directly is a good compromise.
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: MyValidationListIt 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".