Dynamic Graph using INDEX and Named Ranges

[160098]

Hi Guys

I am creating a dynamic graph using a = INDEX (): INDEX (MATCH ()) formula.

I let the range start on a date that is freely selectable and use a =Match() formula to make the range last as far as I want.
An example is attached which shows how it works.
I had to cut some due to formatsize but the idea should be clear. From the data table below the graph I want to display the sales in # or RNs per segment in a stacked area chart.

I define the = INDEX (): INDEX (MATCH ()) as a named range and I use that named range as data input for my graph.

Excel somehow does not accept that I define multiple ranges that way and Excel changes all named ranges to the same definition as the last modified range. This while this construction works on other tabs of the original document.

The only results I have achieved so far do not make any sense, so any help is welcome!

Thanks in advance!

Martijn

[#2]

@160098 

The function you want is OFFSET to create dynamic range names.  I have created an example spreadsheet that shows how it works, including labelling.  Hope you can follow it.

Sure it's volatile, but OFFSET allows you to vary start cells and lengths of ranges.  You can find out more at

https://www.sumproduct.com/thought/onset-of-offset and

https://www.sumproduct.com/thought/dynamic-range-names

[#3]

The issue here is, the full file is already >30 MB. If we add volatile functions the file will take too long to load every time we use it. 

The method I try to use here is a tried method, So the biggest issue I am dealing with is why the named ranges do not follow what I want them to do. 

Whenever I change 1 named range, It changes all the named ranges and I have no clue why this happens. So I got 12 Named ranges one each for every Segment and whenever I name range 1 it is okay. If I add name range 2, the named range 1 will be identical to named range 2. This is where I followed the issue back to and it kinda left me puzzled

Thanks for the help so far! 

[#4]

@160098 

No it won't necessarily - it depends where in the dependency tree the volatility occurs.  Using an array variant of INDEX and three functions (two INDEX and one MATCH ) is not necessarily a great alternative.  INDEX is using an array that could be quite long in memory; OFFSET doesn't work that way.  Sometimes it's a balancing act.  Have you tried seeing how long it would take if you put OFFSET in?  Usually it doesn't cause that much issue if the model is designed well. 

Without seeing the file, I can't explain all your issues, but I can't promise I can solve it if you do post it either :)