Chart Data Source - Updating Last Info

[jevi]

Dear Experts,

I try to find the answer in the old thread not to give you more work but I didnt find .

I have a workbook with 50 worksheet so 50 charts for each branch, and then in the 51 sheet i have the data for each branch. The data is Month, Plan Value and Actual Value. The Plan data is fullfilled from January - December while the Actual Values are updated each month. Now, like for August I have to click on the each chart and change the source data and add august. Is there any way that this can be updated automatically? Like can it take the last value which is fullfilled also in the Actual and Plan?

Thank you a lot,

 

[Trevor G]

You need to look for dynamic charts.

Here is an example of some stages, I also have a sample workbook which deals with Dynamic charges.

Firs the data with the formula

Excel Workbook
	B	C	D	E
5			Dates	Sales
6			01/07/2010	23
7	40392		02/08/2010	34
8			03/09/2010	45
9			05/10/2010	56
10			06/11/2010	67
11			08/12/2010	78
12			09/01/2011	89
13			10/02/2011	100
14			14/03/2011	342
15			15/04/2011	456
16			17/05/2011	500
Charting Using Offset Formula

Next the steps

How this works?

1. The Range has been set through a formula called Offset and Count Range("B7")=OFFSET($D$6,,,COUNT($D$6:$D$34))
2. I am also using named ranges for the Dates and Sales (Date1 and Sales1)
3. As you add more data to the table it will automatically add the new data to the Chart
4.I am setting the Format of the Axis to show only Month and Year. Select the Axis and then Ctrl + 1, Number category and Custom using mmm yyyy and clicking Add
5.I am setting to use random colours and also show values
6. As you add more data in to the Dates and Sales it will add to the Chart​

 

[jevi]

Thank you so so much . It is really a great help for me as i have to do it every month. Just great and a big thnx .

 

[Trevor G]

Pleased to read this has helped you.

 

[jevi]

I try to work on smth else, and not finding it out..I am reading about graphs but not finding the "column rule" is only about "ROW RULE".

If I want the data to be shown as below:

1. Months from Jan-Dec : Row B2-N2
2. Plan from Jan-Dec : Column B3-B57
3. Achievement from Jan-Dec: Column B59-B70 so below the Plan table.

Now: I will create Name Manger

1. Month = OFFSET(Data!$B$1,0,0,COUNTA(Data!$B:$N)-1)
2. Plan = OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1)
3. Achiev =OFFSET(Data!$B$58,0,0,COUNTA(Data!$B:$B)-1)

But is not working in columns only in row, it updates values but if Aug is not yet entered a value the graphs is at 0 and i dont want the graph to be shown when there is no value.

Is there any rule for the formula to give me : A range that starts one Column after B column and whose number of column is one less than the number populated cells in column B.

thnx if you could give a help. Even though the email is long is an issue between "ROW" or "COLUMN"