30 Day Rolling Graph - Part II

%3CLINGO-SUB%20id%3D%22lingo-sub-2380003%22%20slang%3D%22en-US%22%3E30%20Day%20Rolling%20Graph%20-%20Part%20II%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380003%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recently%20reached%20out%20to%20all%20of%20you%20as%20I%20needed%20some%20help%20getting%20things%20going%20with%20rolling%20graphs%20in%20my%20report.%20Mr.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bhelped%20me%20set%20things%20up%20a%20little%20but%20I%20just%20can't%20get%20it%20to%20work%20so%20I%20am%20once%20again%20turning%20to%20the%20community%20for%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAttached%20is%20a%20report%20with%20anonymous%20data.%20I%20have%20six%20tabs%20currently%2C%20and%20for%20two%20I%20need%20rolling%20graphs%20of%20the%20last%2030%20days.%20What%20I%20need%20is%20the%20following%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E-%20Last30A%3A%20This%20tab%20is%20linked%20to%20tab%20DataA%20and%20shows%204%20graphs.%20These%204%20graphs%20would%20need%20to%20be%20rolling%20graphs%20for%20the%20last%2030%20days.%20The%20first%20one%20is%20good%2C%20Graph%20A%2C%20but%20the%20remaining%20three%20are%20not.%20Here's%20the%20rundown%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20A%20%3D%20good%2C%20this%20works%20like%20it%20should%20and%20shows%20exactly%20what%20I%20am%20looking%20for%20in%20the%20other%20graphs%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20B%20%3D%20DataA%20Column%20D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20C%20%3D%20DataA%20Column%20C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20D%20%3D%20DataA%20Column%20E%2FF%2FG%20(combined%20graph%20of%20three%20columns)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E-%20Last30B%3A%20This%20tab%20shows%20a%20similar%20setup%20but%20with%20different%20data.%20I%20need%20all%20four%20graphs%20to%20be%20setup%20as%20rolling%20graphs%20if%20possible.%20Here's%20the%20rundown%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20A%20%3D%20DataB%20Column%20B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20B%20%3D%20DataB%20Column%20D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20C%20%3D%20DataB%20Column%20C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EGraph%20D%20%3D%20DataB%20Column%20E%2FF%2FG%20(combined%20graph%20of%20three%20columns)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20WeeklyA%20and%20WeeklyB%20tabs%20are%20all%20good%2C%20no%20need%20for%20changes%20there.%20The%20DataA%20and%20DataB%20tabs%20contain%20some%20formulas%20so%20please%20do%20not%20change%20them.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20pretty%20sure%20it's%20an%20easy%20fix%20but%20somehow%20I%20just%20couldn't%20figure%20out%20how%20to%20get%20it%20done.%20A%20little%20step%20by%20step%20guide%20is%20welcome%20too%20so%20I%20can%20learn%20how%20to%20fix%20this%20myself.%20Other%20sources%20haven't%20been%20helpfull%20so%20far.%20Much%20appreciated%20yet%20again!%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EI%20am%20working%20on%20a%20laptop%20running%20Windows%2010%2C%20version%2020H2%20with%20Office%20365.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAll%20the%20best%2C%20RNNL21%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2380003%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380307%22%20slang%3D%22en-US%22%3ERe%3A%2030%20Day%20Rolling%20Graph%20-%20Part%20II%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1028407%22%20target%3D%22_blank%22%3E%40RNNL21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20Last30A%3A%3C%2FP%3E%0A%3CP%3EChart%20B%3A%20you%20have%20set%20a%20fixed%20range%20for%20the%20y-values%2C%20instead%20of%20the%20dynamic%20range%20that%20you%20have%20created%20correctly%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0444.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283083i031AFFA567A0C5DE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0444.png%22%20alt%3D%22S0444.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20should%20use%20%3DDataA!DValues%3C%2FP%3E%0A%3CP%3EFor%20chart%20C%2C%20you%20have%20set%20neither%20the%20x-values%20nor%20the%20y-values%20to%20a%20dynamic%20range.%3C%2FP%3E%0A%3CP%3EThe%20problems%20with%20chart%20D%20are%20similar.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Last30B%2C%20you%20need%20to%20set%20up%20a%20series%20of%20defined%20names%20that%20refer%20to%20DataB.%20I%20have%20named%20them%20XValues2%2C%20AValues2%2C%20etc.%20Also%20add%20a%20name%20GValues2%2C%20needed%20for%20chart%20D.%3C%2FP%3E%0A%3CP%3ESince%20row%203%20is%20blank%2C%20the%20ranges%20need%20to%20start%20one%20row%20lower%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3EThen%20use%20these%20in%20the%20source%20of%20the%20charts.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear Community,

 

I recently reached out to all of you as I needed some help getting things going with rolling graphs in my report. Mr. @Hans Vogelaar helped me set things up a little but I just can't get it to work so I am once again turning to the community for help.

 

Attached is a report with anonymous data. I have six tabs currently, and for two I need rolling graphs of the last 30 days. What I need is the following;

 

- Last30A: This tab is linked to tab DataA and shows 4 graphs. These 4 graphs would need to be rolling graphs for the last 30 days. The first one is good, Graph A, but the remaining three are not. Here's the rundown;

 

Graph A = good, this works like it should and shows exactly what I am looking for in the other graphs

Graph B = DataA Column D

Graph C = DataA Column C

Graph D = DataA Column E/F/G (combined graph of three columns)

 

- Last30B: This tab shows a similar setup but with different data. I need all four graphs to be setup as rolling graphs if possible. Here's the rundown:

 

Graph A = DataB Column B

Graph B = DataB Column D

Graph C = DataB Column C

Graph D = DataB Column E/F/G (combined graph of three columns)

 

The WeeklyA and WeeklyB tabs are all good, no need for changes there. The DataA and DataB tabs contain some formulas so please do not change them.

 

I'm pretty sure it's an easy fix but somehow I just couldn't figure out how to get it done. A little step by step guide is welcome too so I can learn how to fix this myself. Other sources haven't been helpfull so far. Much appreciated yet again! I am working on a laptop running Windows 10, version 20H2 with Office 365.

 

All the best, RNNL21

 

 

1 Reply

@RNNL21 

On Last30A:

Chart B: you have set a fixed range for the y-values, instead of the dynamic range that you have created correctly:

S0444.png

You should use =DataA!DValues

For chart C, you have set neither the x-values nor the y-values to a dynamic range.

The problems with chart D are similar.

 

For Last30B, you need to set up a series of defined names that refer to DataB. I have named them XValues2, AValues2, etc. Also add a name GValues2, needed for chart D.

Since row 3 is blank, the ranges need to start one row lower down.

 

See the attached version.

Then use these in the source of the charts.