Home

Dynamic Graph using INDEX and Named Ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-764873%22%20slang%3D%22en-US%22%3EDynamic%20Graph%20using%20INDEX%20and%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-764873%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20dynamic%20graph%20using%20a%20%3D%20INDEX%20()%3A%20INDEX%20(MATCH%20())%20formula.%3C%2FP%3E%3CP%3EI%20let%20the%20range%20start%20on%20a%20date%20that%20is%20freely%20selectable%20and%20use%20a%20%3DMatch()%20formula%20to%20make%20the%20range%20last%20as%20far%20as%20I%20want.%3CBR%20%2F%3EAn%20example%20is%20attached%20which%20shows%20how%20it%20works.%3CBR%20%2F%3EI%20had%20to%20cut%20some%20due%20to%20formatsize%20but%20the%20idea%20should%20be%20clear.%20From%20the%20data%20table%20below%20the%20graph%20I%20want%20to%20display%20the%20sales%20in%20%23%20or%20RNs%20per%20segment%20in%20a%20stacked%20area%20chart.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20define%20the%20%3D%20INDEX%20()%3A%20INDEX%20(MATCH%20())%20as%20a%20named%20range%20and%20I%20use%20that%20named%20range%20as%20data%20input%20for%20my%20graph.%3C%2FP%3E%3CP%3EExcel%20somehow%20does%20not%20accept%20that%20I%20define%20multiple%20ranges%20that%20way%20and%20Excel%20changes%20all%20named%20ranges%20to%20the%20same%20definition%20as%20the%20last%20modified%20range.%20This%20while%20this%20construction%20works%20on%20other%20tabs%20of%20the%20original%20document.%3C%2FP%3E%3CP%3EThe%20only%20results%20I%20have%20achieved%20so%20far%20do%20not%20make%20any%20sense%2C%20so%20any%20help%20is%20welcome!%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3EMartijn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-764873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766355%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Graph%20using%20INDEX%20and%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F363873%22%20target%3D%22_blank%22%3E%40160098%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20function%20you%20want%20is%20%3CSTRONG%3EOFFSET%3C%2FSTRONG%3Eto%20create%20dynamic%20range%20names.%26nbsp%3B%20I%20have%20created%20an%20example%20spreadsheet%20that%20shows%20how%20it%20works%2C%20including%20labelling.%26nbsp%3B%20Hope%20you%20can%20follow%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESure%20it's%20volatile%2C%20but%20%3CSTRONG%3EOFFSET%3C%2FSTRONG%3Eallows%20you%20to%20vary%20start%20cells%20and%20lengths%20of%20ranges.%26nbsp%3B%20You%20can%20find%20out%20more%20at%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sumproduct.com%2Fthought%2Fonset-of-offset%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sumproduct.com%2Fthought%2Fonset-of-offset%3C%2FA%3Eand%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sumproduct.com%2Fthought%2Fdynamic-range-names%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sumproduct.com%2Fthought%2Fdynamic-range-names%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769254%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Graph%20using%20INDEX%20and%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769254%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20issue%20here%20is%2C%20the%20full%20file%20is%20already%20%26gt%3B30%20MB.%20If%20we%20add%20volatile%20functions%20the%20file%20will%20take%20too%20long%20to%20load%20every%20time%20we%20use%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20method%20I%20try%20to%20use%20here%20is%20a%20tried%20method%2C%20So%20the%20biggest%20issue%20I%20am%20dealing%20with%20is%20why%20the%20named%20ranges%20do%20not%20follow%20what%20I%20want%20them%20to%20do.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20I%20change%201%20named%20range%2C%20It%20changes%20all%20the%20named%20ranges%20and%20I%20have%20no%20clue%20why%20this%20happens.%20So%20I%20got%2012%20Named%20ranges%20one%20each%20for%20every%20Segment%20and%20whenever%20I%20name%20range%201%20it%20is%20okay.%20If%20I%20add%20name%20range%202%2C%20the%20named%20range%201%20will%20be%20identical%20to%20named%20range%202.%20This%20is%20where%20I%20followed%20the%20issue%20back%20to%20and%20it%20kinda%20left%20me%20puzzled%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help%20so%20far!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769284%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Graph%20using%20INDEX%20and%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F363873%22%20target%3D%22_blank%22%3E%40160098%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20it%20won't%20necessarily%20-%20it%20depends%20where%20in%20the%20dependency%20tree%20the%20volatility%20occurs.%26nbsp%3B%20Using%20an%20array%20variant%20of%20INDEX%20and%20three%20functions%20(two%20INDEX%20and%20one%20MATCH%20)%20is%20not%20necessarily%20a%20great%20alternative.%26nbsp%3B%20INDEX%20is%20using%20an%20array%20that%20could%20be%20quite%20long%20in%20memory%3B%20OFFSET%20doesn't%20work%20that%20way.%26nbsp%3B%20Sometimes%20it's%20a%20balancing%20act.%26nbsp%3B%20Have%20you%20tried%20seeing%20how%20long%20it%20would%20take%20if%20you%20put%20OFFSET%20in%3F%26nbsp%3B%20Usually%20it%20doesn't%20cause%20that%20much%20issue%20if%20the%20model%20is%20designed%20well.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithout%20seeing%20the%20file%2C%20I%20can't%20explain%20all%20your%20issues%2C%20but%20I%20can't%20promise%20I%20can%20solve%20it%20if%20you%20do%20post%20it%20either%20%3A-)%3C%2FP%3E%3C%2FLINGO-BODY%3E
160098
New Contributor

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

3 Replies

@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

 

 

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! 

@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 :-)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies