Help with dynamic chart

[tomleitch]

Hi folks.

I am wondering if anyone can help with this, it's maybe a bit of a complicated one - I'm not sure.

I am trying to make a chart showing 12 months worth of data - a rolling 12 month trend.... to show oil usage based on top up volumes.

All the data that will be input is when it is topped up, so there will be the date in one column and how much was put in in another.

The trouble is that these maybe done every day, once a month or once a week etc.

What I want to have is a chart that dynamically updates to show all the values of the last 12 months only.

Is this possible?.... any help greatly appreciated


Many Thanks,
Tom

 

[Worf]

Hi Tom

· Will the dates be inputted in ascending order? If not, can we sort them?
· Would you accept a VBA solution? Do you need to do it only with formulas?

 

[tomleitch]

Hi Worf

Thanks for looking at this for me.

Yeah VBA would be great..... basically what I was thinking (and I don't know if this is the best way and I'm not very experienced with VBA) was that I could use VBA to select a range of dates that are within the last year and plot them across the X axis. Kind of like a real time trend that would refresh every time the workbook was opened.

The dates will all be in chronological order so the bottom row will be the newest entry.


Any advice very much appreciated.

Thanks
Tom

 

[fizzickschick]

I create a new file for every calendar year (manufacturing production data) so I don't need a dynamic range. the range for my chart is jan thru dec. and a data point appears on the chart for each month, only when there is data in that cell. so every month I paste in the data, and the chart updates itself. if it was weekly, I would do it weekly. also, managers need to CTFO when asking for charts. pick one and stick with it. you want it daily then say so. you wanna hire someone with no programming background to basically create a dashboard for you while also assisting everyone in using the copier and making coffee and answering the phones, well, you get what you pay for. sorry a little personal!

 

[Worf]

- I decided to do it with formulas.
- The basic technique is described here:http://www.marcusoft.net/2015/10/making-dynamic-chart-in-excel.html
- Cell X22 calculates the starting row for the chart range.
- Create the following named ranges and link them to the chart:

mydates=OFFSET(Bars!$Z$1,Bars!$X$22,0,COUNTA(Bars!$Z:$Z)-Bars!$X$22)
myvalues=OFFSET(Bars!$AA$1,Bars!$X$22,0,COUNTA(Bars!$AA:$AA)-Bars!$X$22)

- Add values on the worksheet and the chart will automatically update.

Bars

*	X	Y	Z	AA
1	*	*	03/07/2015	3
2	*	*	07/08/2015	5
3	*	*	11/09/2015	8
4	*	*	16/10/2015	9
5	*	*	20/11/2015	4
6	*	*	25/12/2015	6
7	*	*	29/01/2016	7
8	*	*	04/03/2016	3
9	*	*	08/04/2016	4
10	*	*	13/05/2016	5
11	*	*	17/06/2016	6
12	*	*	22/07/2016	7
13	*	*	26/08/2016	7
14	*	*	30/09/2016	9
15	*	*	04/11/2016	4
16	*	*	09/12/2016	6
17	*	*	13/01/2017	3
18	*	*	*	*
19	*	*	*	*
20	*	*	*	*
21	*	*	*	*
22	5	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula X22 =MATCH(VLOOKUP(INDEX(Z1:Z1000,COUNTA(Z:Z),1)-365,Z1:Z1000,1,TRUE),Z1:Z1000,0)-1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4