=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/