Graph Help- 12 month graph, but only want months that have passed

C

coal_miner

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.
 
C

coal_miner

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
 
A

Andy Pope

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
 
C

coal_miner

Thank you Andy, Im gonna give it a try.

Andy Pope said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top