Forum Discussion
Dynamic Graph using INDEX and Named Ranges
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
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!
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 :-)
