getting a range of cells from calculation
 

basically i have a big table with dates along the top. what im going t
do is calculate which columni want to use for the source to a chart, b
doing something like:

required column=date in first column-current date

this will give me how many columns to go accross from the first colum
to reach todays one. BUT what I then need to do is get the range o
cells underneath that column from row 4 down to the last non blan
cell.

I need this to all be automated i.e. somehow get the chart to be base
on the result of this calculation. there will be two calculatoins on
for planned and one for actual and these will be 2 bars on the chart.
i could put the resulting range for the planned and the actual into tw
cells but i cant base the graph on whatever range is contained withi
those two cells can i? if i can then this would be the easiest way.

thank

--
Message posted from http://www.ExcelForum.com

getting a range of cells from calculation
 

=Offset($A$4,0,$A$1-Date,counta(Offset($A$4,0,$A$1-Date,10000)),1)

Assumes filled cells in a column are contiguous (no blanks until the last)
and no column will have more than 10000 entries.


if you want to put the range in cells
assume the range is in C2

=Indirect(C2)



--
Regards,
Tom Ogilvy

"neowok " wrote in message
...
basically i have a big table with dates along the top. what im going to
do is calculate which columni want to use for the source to a chart, by
doing something like:

required column=date in first column-current date

this will give me how many columns to go accross from the first column
to reach todays one. BUT what I then need to do is get the range of
cells underneath that column from row 4 down to the last non blank
cell.

I need this to all be automated i.e. somehow get the chart to be based
on the result of this calculation. there will be two calculatoins one
for planned and one for actual and these will be 2 bars on the chart.
i could put the resulting range for the planned and the actual into two
cells but i cant base the graph on whatever range is contained within
those two cells can i? if i can then this would be the easiest way.

thanks


---
Message posted from http://www.ExcelForum.com/