I don't seem to use the dynamic ranges with #, which works fine in formula. Is this a known issue in charts? Is there any workaround?To reproduce the problem, we create two sequences in A2 and B2 with =SEQUENCE(25,1,5,2) and =SEQUENCE(25,1,25,5) and use A2# and B2# in the chart "Select Data".

SergeiBaklan 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.

SergeiBaklan 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 addDespite 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.

cmukesh19 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).

cmukesh19 This is a known limitation. As a workaround, create a named range X_Values that refers to =$A$2#, and another named range Y_Values that refers to =$B$2#. You can use X_Values and Y_Values in the Select Data dialog. See the attached demo workbook.

Charts do not accept ranges with hash #

12 Replies

SergeiBaklan
Diamond Contributor
Sep 17, 2022
cmukesh19

With latest Beta charts are dynamically changes if spill size is changed, similar to tables. In initial chart use B2:B10, whatever, not B2#. If spill will be changed on B2:C14, chart will be shown for the latest.
- cmukesh19
  Copper Contributor
  Sep 18, 2022
  It doesn't work in all cases.
  Try putting number in cell A1 (use 50).
  Use two sequences in cell B1 and C1 as =SEQUENCE(A1,1,5,5) and =SEQUENCE(A1,1,17,9). Build a chart using B1:B50 and C1:C50.
  Now change cell A1 to 100, which will make both the sequences to generate 100 elements. The chart will still use only 50 elements.
  - SergeiBaklan
    Diamond Contributor
    Sep 19, 2022
    cmukesh19
    
    In my case it works
    
    Even if you are on Beta channel new functionality is deployed gradually, hope will come soon.
- PeterBartholomew1
  Silver Contributor
  Sep 17, 2022
  SergeiBaklan
  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.
  - SergeiBaklan
    Diamond Contributor
    Sep 17, 2022
    PeterBartholomew1
    
    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.
PeterBartholomew1
Silver Contributor
Sep 17, 2022
cmukesh19
Just an added thought based on HansVogelaar 's strategy. If you use sheet-local names rather than workbook scoped names, the fully qualified names used by the chart engine to address data can be shorter and more meaningful. For example, if the sheet tab shows "cht", the series would read
=SERIES(,cht!x,cht!y,2)
HansVogelaar
MVP
Sep 17, 2022
cmukesh19

This is a known limitation. As a workaround, create a named range X_Values that refers to =$A$2#, and another named range Y_Values that refers to =$B$2#.

You can use X_Values and Y_Values in the Select Data dialog.

See the attached demo workbook.
SpillRangechart.xlsx15 KB

Forum Discussion

Charts do not accept ranges with hash #

12 Replies

Resources