Dynamic chart help

Question

I am using excel 2007

I am trying to create a dynamic chart that will update automatically when I enter data in for the new month. Below is a sample of the data:

	Jan	Feb	Mar	Apr	May	Jun	July	Aug	Sept	Oct	Nov	Dec
Actual	8000	4500	2000	8500	3200	6100
Plan	7500	5000	4000	6000	3000	6000
Variance	-500	500	2000	-2500	-200	-100

I have formulas pull the data in when it gets loaded into the spreadsheet. 

In a nutshell I want to create a chart that includes the entire year but only show months with data. If I create a chart like this it will show a bar graph with data in the first 6 months and nothing in the second 6 months. I want to hide the second 6 months until data has been entered.

Please help!

Thanks in advance!

Answer 1

Hi,

I have tried and tested this functionality in Excel 2013.  I am not sure of whether this will work in Excel 2007 or not.

1. Suppose June data is in column G
2. Ensure the headings in A1:G1 are in some typical formatting such as Bold, Italic
3. Select A1:G4 and press Ctrl+T > OK
4. Create a Graph out of this data
5. Now type the month in cell H1 and enter data below this heading
6. The graph should auto update with this new column

Hope this helps.

Answer 2

1. Let's assume your data is in range A1 to M4.
2. Formulas tab > Name Manager - Put following in Name & Refers to

Actual_D         =OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$B$2:$M$2))

Month_D        =OFFSET(Sheet1!$A$1,0,1,1,COUNTA(Sheet1!$B$2:$M$2))

Plan_D           =OFFSET(Sheet1!$A$3,0,1,1,COUNTA(Sheet1!$B$3:$M$3))

Variance_D     =OFFSET(Sheet1!$A$4,0,1,1,COUNTA(Sheet1!$B$4:$M$4))

Names can be anything. I have just put it for the sake of remembering it properly.

(Note - Shee1! is redundant. You can put formulas without them also. But, Name Manager will insert Sheet Name on its own when you close Name Manager)

3. Draw the chart by taking complete range or partial range.
4. Right click the chart and take Select Data
5. Edit the Series one by one. In place of Range, put names created in step 2. (Make sure Sheet1! is preserved. This is not optional. If Sheet name is not put, it will give error)

6. Do the same for Horizontal Axis Labels.
7. If everything has gone fine, then you have your dynamic chart.

I had made the sheet and it is uploaded at following place. You can download and learn from this -

https://onedrive.live.com/redir?resid=E11B26EEAACB7947%21181

Answer 3

Create a dynamic named range, say, with the name

Actual

using the formula

=OFFSET(Cell with Actual,0,1,1, COUNTA(row with Actual)-1)

like

=OFFSET($A$2,0,1,1, COUNTA(2:2)-1)

and use that as the source range.  And the same for the others

Answer 4

You need a Dynamic Range for the series.

Stephen Bullen shows you how.

http://www.oaltd.co.uk/Excel/

Download FunChrt1.zip  sample workbook.

Gord