I have dates in the first row
I have values in the second row
I use tecnique of named formuls of offset function (tushar Methta) so that
the chart is updated when data for new dates are added.
now after some time I want to delete the older data
If I delete the columns of older dates the named formula get messed up and I
get error message that three is some wrong reference. actulally in the
offset formula the starting cell is relaced by "REF"
Then I have to redo the whole exercise
any solutions.

If the first row is some sort of header that doesn't get deleted, use
it as the base cell in the OFFSET function. So, if B1 is a header and
you wish to plot the contents of column B, use
YVals =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

If not, use the INDIRECT function. To refer to column B use
YVals =OFFSET(INDIRECT("sheet1!b1"),0,0,COUNTA(Sheet1!$B:$B),1)

The big downside is the need to add the worksheet name.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <#(E-Mail Removed)>, $$$$vram26@vsnl
says...
> I have dates in the first row
> I have values in the second row
> I use tecnique of named formuls of offset function (tushar Methta) so that
> the chart is updated when data for new dates are added.
> now after some time I want to delete the older data
> If I delete the columns of older dates the named formula get messed up and I
> get error message that three is some wrong reference. actulally in the
> offset formula the starting cell is relaced by "REF"
> Then I have to redo the whole exercise
> any solutions.
>
>
>

unfortunately for me the first row which is of course a header also gets
deleted
my firstr row is dates --. 1 apr, 2 apr etc. my second, third etc rows are
data for each item (in my case mutual funds). I dont want to have the
graphs for the whole year
when after a few days in May I would like to remove the april data and have
the chart only for May. In the circumstances I believe only your valuable
suggestion of using indirect function will be useful with slight
modification for row-wise data. .Or I should use A1 as thebase cell for
offset provided I dont delete col A or row 1. I have already tried indirect
function and it is ok. thanks for the valuable suggestion
Only thing is I have not yet completely undersood all the potentialities of
Indirect function. I need to further study.
===================================


Tushar Mehta <(E-Mail Removed)> wrote in message
news:MPG.1b09b3cfac8903d29897a8@news-server...
> If the first row is some sort of header that doesn't get deleted, use
> it as the base cell in the OFFSET function. So, if B1 is a header and
> you wish to plot the contents of column B, use
> YVals =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
>
> If not, use the INDIRECT function. To refer to column B use
> YVals =OFFSET(INDIRECT("sheet1!b1"),0,0,COUNTA(Sheet1!$B:$B),1)
>
> The big downside is the need to add the worksheet name.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <#(E-Mail Removed)>, $$$$vram26@vsnl
> says...
> > I have dates in the first row
> > I have values in the second row
> > I use tecnique of named formuls of offset function (tushar Methta) so
that
> > the chart is updated when data for new dates are added.
> > now after some time I want to delete the older data
> > If I delete the columns of older dates the named formula get messed up
and I
> > get error message that three is some wrong reference. actulally in the
> > offset formula the starting cell is relaced by "REF"
> > Then I have to redo the whole exercise
> > any solutions.
> >
> >
> >