Dynamic Chart Range for YTD

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
91
Hi all,

I am currently going through the process of making charts dynamic by using named ranges and the OFFSET function.

Most of our charts show 13 months and I have achieved that with a named range called Months_13 which is defined as below.
=OFFSET('Input Screen With Targets'!$B$1,0,COUNTA('Input Screen With Targets'!$1:$1)-1,1,-14)

The problem I am now faced with though is that we have various charts which are for year to date (financial year starting in April). The spreadsheet contains data from all the way back to 2013. I need to start from April 2018 for this year but also I would like it to update so that when we enter April 2019 the range is automatically updated for the current year.

As always any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
Feb 17Mar 17Apr 17May 17Jun 17Jul 17Aug 17Sep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Aug 18Sep 18Oct 18Nov 18Dec 18Jan 19Feb 19
67.67359.92466.64654.97769.00558.06959.98268.60354.16556.61159.69463.23866.69555.83257.71362.143253.06359.13856.74450.49752.23368.25161.72962.3364.001
fin yr start date
Apr 17apr 17 is in cell K9
123456789101112
Apr 17May 17Jun 17Jul 17Aug 17Sep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18
66.64654.97769.00558.06959.98268.60354.16556.61159.69463.23866.69555.832
formula giving april 17
=OFFSET($A$2,0,MATCH($K$9,$B$2:$Z$2,0)+E11-1)
formula giving 51.864
=OFFSET($A$2,1,MATCH(E12,$B$2:$Z$2,0))

<colgroup><col><col span="3"><col><col><col span="10"><col><col span="9"></colgroup><tbody>
</tbody>
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
91
I'm afraid I don't understand. I am trying to specify a range for a chart to read. Unless I misunderstanding, this appears to be creating a new table for this purpose. Is there a way of doing it without having to input the start date and creating a new table? We will only ever be interested in the current year so no need to be able to select a start date.
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
Feb 17Mar 17Apr 17May 17Jun 17Jul 17Aug 17Sep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Aug 18Sep 18Oct 18Nov 18Dec 18Jan 19Feb 19Mar 19
67.750.0463.39569.91253.363.0281864.0260.66954.15253.76950.100494861.18653.46656.41452.89567.259458.8254.56859.10264.0655.963151758.339125452.77763.70264.72977.7
mytable
01/04/201601/04/2016
01/04/201701/04/2017
ok it is now fully dynamic - it is generated by today's date01/04/201801/04/2018
a small lookup table generates the financial year start date01/04/201901/04/2019
aprmayjunjulyaugsepoctnovdecjanfebmar
52.9067.2658.8254.5759.1064.0655.9658.3452.7863.7064.7377.70
65.30 obtained by
=OFFSET($A$2,1,MATCH(VLOOKUP(TODAY(),mytable,2),$B$2:$AA$2,0)+COLUMN()-7)

<colgroup><col><col span="3"><col><col><col><col span="4"><col><col span="4"><col><col span="4"><col><col><col span="7"></colgroup><tbody>
</tbody>
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
91
No, I still don't get it :( Perhaps I am being thick.

All I want to do is define a range. So as it is January '19, that range will run from April '18 to January '19.

I have figured out how to get the last 13 months using
=OFFSET('Input Screen With Targets'!$B$1,0,COUNTA('Input Screen With Targets'!$1:$1)-1,1,-14)

I need a similar formula that will create a range that ends in the same place but only goes as far back as April. At the moment that would give me 10 months in my range. If the last entry is April then it will only show a single month. I was thinking that there may be a way to replace the '-14' part of the formula with a method to count the number of months since the latest April but this is where I have hit a brick wall.

Once I have the range defined I can easily pull the figures by offsetting from it.

Thanks for your patience.
 

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
91
Nevermind...I have figured it out.

I created a helper row below the dates, counting the number of months to show for financial year. I then looked up the last entry in that row and counted back by the result +1 to give me my range length.
=OFFSET('Input Screen With Targets'!$B$1,0,COUNTA('Input Screen With Targets'!$1:$1)-1,1,-LOOKUP(2,1/('Input Screen With Targets'!$2:$2<>""),'Input Screen With Targets'!$2:$2+1))

Thanks for your time anyway.
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
I made a range of months and a data value from a formula that looked at your figures - the chart could run off that dynamically
 

Watch MrExcel Video

Forum statistics

Threads
1,095,685
Messages
5,445,946
Members
405,371
Latest member
Joe Zawadzki

This Week's Hot Topics

Top