I need help setting up a graph. It is a simple bar graph, that has the
months of the year on the x axis, and production in tons on the y axis. I
have a table setup for this Jan to Dec, but obviously only have data for Jan
through May. Is there a way I can set up the graph so it will only make the
graph Jan through May, but as I add more data, and months go by, it will
automatically add a new column for June, then July and so on and so on? I do
not want the months that have no data to be graphed with a value of zero, I
would rather them show up automatically when that month has data. Any help
would be appreciated. Thanks.

Hi,

Have a look at creating dynamic named ranges, here are 2 examples.
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

coal_miner wrote:
I need help setting up a graph. It is a simple bar graph, that has the
months of the year on the x axis, and production in tons on the y axis. I
have a table setup for this Jan to Dec, but obviously only have data for Jan
through May. Is there a way I can set up the graph so it will only make the
graph Jan through May, but as I add more data, and months go by, it will
automatically add a new column for June, then July and so on and so on? I do
not want the months that have no data to be graphed with a value of zero, I
would rather them show up automatically when that month has data. Any help
would be appreciated. Thanks.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thank you this is helpful. One problem though is one my workbook I have the
months listed on the x-axis across the top, but do not have any data in the
individual cells within the columns. I need to leave the months of the
future on the workbook, so how can I leave them there, and it will not graph
them until there is data in the cells corresponding with the month? Is it
possible? Thanks

"Andy Pope" wrote:

Hi,

Have a look at creating dynamic named ranges, here are 2 examples.
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

coal_miner wrote:
I need help setting up a graph. It is a simple bar graph, that has the
months of the year on the x axis, and production in tons on the y axis. I
have a table setup for this Jan to Dec, but obviously only have data for Jan
through May. Is there a way I can set up the graph so it will only make the
graph Jan through May, but as I add more data, and months go by, it will
automatically add a new column for June, then July and so on and so on? I do
not want the months that have no data to be graphed with a value of zero, I
would rather them show up automatically when that month has data. Any help
would be appreciated. Thanks.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Base the named range for the category labels on the cells with data
rather than the actual label cells. Did that make sense ???

For example, A1:A12 contains the month names and B1:B12 will contain
monthly data as and when. So usually the named range would be something
like,

ChtLabels: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$12), 1)
ChtData: =OFFSET(Sheet1!$B$1,0,0,COUNT(Sheet1!$B$1:$B$12),1 )

But the ChtLabels would give you all 12 months straight away, so instead
modify that to use the same test as ChtData.

ChtLabels: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B$1:$B$12), 1)

or even better, once CHtData is defined,
ChtLabels: =OFFSET(ChtData,0,-1)

Cheers
Andy


coal_miner wrote:
Thank you this is helpful. One problem though is one my workbook I have the
months listed on the x-axis across the top, but do not have any data in the
individual cells within the columns. I need to leave the months of the
future on the workbook, so how can I leave them there, and it will not graph
them until there is data in the cells corresponding with the month? Is it
possible? Thanks

"Andy Pope" wrote:


Hi,

Have a look at creating dynamic named ranges, here are 2 examples.
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

coal_miner wrote:

I need help setting up a graph. It is a simple bar graph, that has the
months of the year on the x axis, and production in tons on the y axis. I
have a table setup for this Jan to Dec, but obviously only have data for Jan
through May. Is there a way I can set up the graph so it will only make the
graph Jan through May, but as I add more data, and months go by, it will
automatically add a new column for June, then July and so on and so on? I do
not want the months that have no data to be graphed with a value of zero, I
would rather them show up automatically when that month has data. Any help
would be appreciated. Thanks.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thank you Andy, Im gonna give it a try.

"Andy Pope" wrote:

Base the named range for the category labels on the cells with data
rather than the actual label cells. Did that make sense ???

For example, A1:A12 contains the month names and B1:B12 will contain
monthly data as and when. So usually the named range would be something
like,

ChtLabels: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$12), 1)
ChtData: =OFFSET(Sheet1!$B$1,0,0,COUNT(Sheet1!$B$1:$B$12),1 )

But the ChtLabels would give you all 12 months straight away, so instead
modify that to use the same test as ChtData.

ChtLabels: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B$1:$B$12), 1)

or even better, once CHtData is defined,
ChtLabels: =OFFSET(ChtData,0,-1)

Cheers
Andy


coal_miner wrote:
Thank you this is helpful. One problem though is one my workbook I have the
months listed on the x-axis across the top, but do not have any data in the
individual cells within the columns. I need to leave the months of the
future on the workbook, so how can I leave them there, and it will not graph
them until there is data in the cells corresponding with the month? Is it
possible? Thanks

"Andy Pope" wrote:


Hi,

Have a look at creating dynamic named ranges, here are 2 examples.
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

coal_miner wrote:

I need help setting up a graph. It is a simple bar graph, that has the
months of the year on the x axis, and production in tons on the y axis. I
have a table setup for this Jan to Dec, but obviously only have data for Jan
through May. Is there a way I can set up the graph so it will only make the
graph Jan through May, but as I add more data, and months go by, it will
automatically add a new column for June, then July and so on and so on? I do
not want the months that have no data to be graphed with a value of zero, I
would rather them show up automatically when that month has data. Any help
would be appreciated. Thanks.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info