Dynamic Chart Range for YTD

[Dan5977]

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.

 

[oldbrewer]

	Feb 17	Mar 17	Apr 17	May 17	Jun 17	Jul 17	Aug 17	Sep 17	Oct 17	Nov 17	Dec 17	Jan 18	Feb 18	Mar 18	Apr 18	May 18	Jun 18	Jul 18	Aug 18	Sep 18	Oct 18	Nov 18	Dec 18	Jan 19	Feb 19
	67.673	59.924	66.646	54.977	69.005	58.069	59.982	68.603	54.165	56.611	59.694	63.238	66.695	55.832	57.713	62.1432	53.063	59.138	56.744	50.497	52.233	68.251	61.729	62.33	64.001
										fin yr start date
										Apr 17			apr 17 is in cell K9
				1	2	3	4	5	6	7	8	9	10	11	12
				Apr 17	May 17	Jun 17	Jul 17	Aug 17	Sep 17	Oct 17	Nov 17	Dec 17	Jan 18	Feb 18	Mar 18
				66.646	54.977	69.005	58.069	59.982	68.603	54.165	56.611	59.694	63.238	66.695	55.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]

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]

	Feb 17	Mar 17	Apr 17	May 17	Jun 17	Jul 17	Aug 17	Sep 17	Oct 17	Nov 17	Dec 17	Jan 18	Feb 18	Mar 18	Apr 18	May 18	Jun 18	Jul 18	Aug 18	Sep 18	Oct 18	Nov 18	Dec 18	Jan 19	Feb 19	Mar 19
	67.7	50.04	63.395	69.912	53.3	63.02818	64.02	60.669	54.152	53.769	50.1004948	61.186	53.466	56.414	52.895	67.2594	58.82	54.568	59.102	64.06	55.9631517	58.3391254	52.777	63.702	64.729	77.7
																					mytable
																					01/04/2016	01/04/2016
																					01/04/2017	01/04/2017
							ok it is now fully dynamic - it is generated by today's date								01/04/2018	01/04/2018
							a small lookup table generates the financial year start date								01/04/2019	01/04/2019
						apr	may	jun	july	aug	sep	oct	nov	dec	jan	feb	mar
						52.90	67.26	58.82	54.57	59.10	64.06	55.96	58.34	52.78	63.70	64.73	77.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]

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]

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]

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