Auto updating graph

[penfold1992]

I have a graph that looks at a some standard values that correspond with a week number.
so I have 52 weeks each with a number set to it.

I would like a graph that takes in account the current week number and plots the corresponding value as well as the 5 previous values (rolling 6 weeks)

I could do this manually by selecting the graph's data and changing it to the next 6 weeks needed however this is time consuming.

so is it possible to do this via VBA or even without vba =S

[Jacc]

Is it something like this you want? I wasn't sure if you meant just one value for each week or several so here is several.

[penfold1992]

Is it something like this you want? I wasn't sure if you meant just one value for each week or several so here is several.

YES! this is very very close to what I need!
please can you explain how a few of these work... first is the data table (why is K1:P1 2-7?)
also can you explain how you made those buttons next to it?

I assume the graph is a simple set up though?
thanks so much this is very promising!

[Jacc]

I use the INDEX function to retrieve data from the datalist. If you look in, let's say cell M3, you'll see this formula:

Please Login or Register  to view this content.

(due to different language settings my formulas have semicolon where yours may have comma)
The $A2:$G10000 is the range where the data is found. Note the dollar signs, they say that the range should stay fixed even as i copy the formula along rows and columns.

The INDEX function will retrieve data from the datarange rownumber given in $J3 column number given in M$1. The $ signs lock the column and the row respectively so that the formula can just be copied across both columns and rows. Play with it and you'll figure it out. Use the F4 key to cycle through $ signs.

I added the row of grey numbers in K1:P1 so that I could conveniently just copy the formula across the columns without having to type the number 1 to 7 in the formula in each cell. There are other methods to solve this but I choose this one because it may seem easier for the less experienced exceler.

By clicking in the formula list, the involved areas will be marked by a colored frame, see this picture: Dynamic chart week formula.png
This makes formulas easier to read.

To find the spin button you first have to add the developers tab like this: http://www.youtube.com/watch?v=SdxNopUuoS0
The spin button can then be found here: Spin button.png
Once you added the button, right click on it and choose Format Control and then the Control tab. It's pretty self explaining from there on.

The spin button control cell J8. In cell J7 I just added the formula:

Please Login or Register  to view this content.

and copied it up another 4 cells.

In cell R7 I build up the chart title like this:

Please Login or Register  to view this content.

The & sign concatenates the contents of different cells. Note leading and trailing space in R6.
Click on the chart title, type = and then select cell R7 to get the dynamic chart title.

[penfold1992]

Thanks for the explanation it really helped!
I have just one last question for you (which is silly really)

in my opinion the up and down controls are the opposite of what they should do... is it possible to reverse it?
(so UP does what DOWN does and visa versa)

[Jacc]

Good to hear it helped!

A rather relevant question I'd say. You can't make the Form controls spinbutton go the other way directly. However, as a workaround you can assign the spin button to cell I8 and the enter the formula:

Please Login or Register  to view this content.

in cell J8. This will reverse the control. The number 25 has to chosen to fit your needs ofcourse.

Another option is to use the Active X spinbutton, you'll find it in the same menu as the Form controls spinbutton. This spinbutton is slightly harder to use but it can be reversed by just swapping the upper and lower limits around. You do need to use the Design Mode button next the the controls menu in order to edit it.

[Jacc]

I assume everything works since you disappeared. Don't forget to mark the thread SOLVED if it solved your problems.