Jul 19 2019 08:00 AM
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
Jul 21 2019 05:15 AM
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
Jul 23 2019 03:14 AM
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!
Jul 23 2019 03:49 AM
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 :)