cell reference within cell reference

  • Thread starter Thread starter jamesstout222
  • Start date Start date
J

jamesstout222

i have x values and y values in columns and i add more values everyday
I want to have a XY scatter chart which only shows the last 25
entries. Therefore I'm trying to do something like this:

cell E1 contains the number of the most recent entry eg 350


x values - A(E1-250) : A(E1)
eg would be A100:A350


y values - B(E1-250) : B(E1)
eg would be B100:B350

this won't work. Any idea how i can fiddle this
 
I am going to assume your x-values start in A2, y-values in B2, all on
Sheet5
Use Insert|Name|Define and define MyX as
=OFFSET(Sheet5!$A$2,COUNTA(Sheet5!$A$2:$A$6500)-250,0,250)
Use Insert|Name|Define and define MyY as
=OFFSET(Sheet5!$B$2,COUNTA(Sheet5!$A$2:$A$6500)-250,0,250)
Make a chart of ALL of your data - ignore the mess!!!
Right click chart, from popup menu select Source Data
Set the x-range to Sheet5!MyX and the y-range to Sheet5!MyY
Hey presto, we are done

Best wishes
 
Back
Top