Hi MS Office Help,

I would like to know how to choose a selection of cells based on what month
it is in the real world.

For instance, if the month was january, excel would allow me to select cells
A1-F1, which I could then use as a data source for a chart. If the month
became march or april, excel would allow me to select cells C1-H1, D1-I1
respectively, and so on. This will allow me to vary my chart automatically as
the month changes, which will be useful when I need to make monthly statements.

Help is greatly appreciated.

Solomon -

One way is to use a set of unused cells to house the data for the chart.
For example, if you want to always chart 6 values, then you can set up a
worksheet with the data in cells AA2 to AF2, and the headings in cells AA1 to
AF1. Then this is what you put in cell AA2 (the first data cell):
=INDIRECT("R1C"&MONTH(NOW()),FALSE)
Explanation: This cell looks at today's date Now() and gets the month
number Month and appends that to a relative address. For January this
would be R1C1, for February this would be R1C2, etc. Then the INDIRECT will
put the value of the cell into the current cell (AA2).

So, you can set up the 5 cells in a similar manner. If you need column
titles to change so you can use them on the chart, then you can do the same
for cells AA1 to AF1.

Now, point your chart to use these cells, and as the months change, so will
these values, and your chart.

--
Daryl S


"Solomon" wrote:

Hi MS Office Help,

I would like to know how to choose a selection of cells based on what month
it is in the real world.

For instance, if the month was january, excel would allow me to select cells
A1-F1, which I could then use as a data source for a chart. If the month
became march or april, excel would allow me to select cells C1-H1, D1-I1
respectively, and so on. This will allow me to vary my chart automatically as
the month changes, which will be useful when I need to make monthly statements.

Help is greatly appreciated.

Thank you, this is very useful. I can clearly see where to go from here.

"Daryl S" wrote:

Solomon -

One way is to use a set of unused cells to house the data for the chart.
For example, if you want to always chart 6 values, then you can set up a
worksheet with the data in cells AA2 to AF2, and the headings in cells AA1 to
AF1. Then this is what you put in cell AA2 (the first data cell):
=INDIRECT("R1C"&MONTH(NOW()),FALSE)
Explanation: This cell looks at today's date Now() and gets the month
number Month and appends that to a relative address. For January this
would be R1C1, for February this would be R1C2, etc. Then the INDIRECT will
put the value of the cell into the current cell (AA2).

So, you can set up the 5 cells in a similar manner. If you need column
titles to change so you can use them on the chart, then you can do the same
for cells AA1 to AF1.

Now, point your chart to use these cells, and as the months change, so will
these values, and your chart.

--
Daryl S


"Solomon" wrote:

Hi MS Office Help,

I would like to know how to choose a selection of cells based on what month
it is in the real world.

For instance, if the month was january, excel would allow me to select cells
A1-F1, which I could then use as a data source for a chart. If the month
became march or april, excel would allow me to select cells C1-H1, D1-I1
respectively, and so on. This will allow me to vary my chart automatically as
the month changes, which will be useful when I need to make monthly statements.

Help is greatly appreciated.