Changing cell references in graph range (or any formula for that matter)

I am sure this question has been posed previously and may seem a
stupid question, however I am really struggling to find the answer?
Could anyone please help me!?
If I have the simple formula in for a graph
range=SERIES(,,'1(100)'!$AN$2:$AN$79,1)
I am generating random numbers that vary the resultant graph each time
from the automatic r-ecalculation which give me a different last cell
range each re-calculation; which effects or changes only the AN79
which now may becomes let's say AN65 or whatever. I know only the row
changes from 79 to 65 - that row ending reference is also given in
another cell after the re-calculation separate from the range - which
will say be 65 for this example. How do I transpose that range to
detect the reference of 65 without manually double clicking the line
and overwriting the 79 with a 65 each time?
Similarly and perhaps to simplify the question - the same logic could
be applied for lets say in cell a9 we have a formual =sum(a1:a8) - the
row 8 changes to lets say row 4 how do I get the program to enter the
4 without the manual route of manually changing the formula in cell
a9?
Thanks any help if a nudge in the right direction will be kindly
appreciated?
Allan Howden

You could think of entering in the cell linked to the graph a formula that
choose the value from the appropiate cells and rows depending on the results
of your calculation. By that way, Your graph will point always to the same
cell. and the cell will do the work of choosing the correct row, column for
the table of value that your are recalculating.
regards,
Yves

"Allan Howden" wrote in message
om...
I am sure this question has been posed previously and may seem a
stupid question, however I am really struggling to find the answer?
Could anyone please help me!?
If I have the simple formula in for a graph
range=SERIES(,,'1(100)'!$AN$2:$AN$79,1)
I am generating random numbers that vary the resultant graph each time
from the automatic r-ecalculation which give me a different last cell
range each re-calculation; which effects or changes only the AN79
which now may becomes let's say AN65 or whatever. I know only the row
changes from 79 to 65 - that row ending reference is also given in
another cell after the re-calculation separate from the range - which
will say be 65 for this example. How do I transpose that range to
detect the reference of 65 without manually double clicking the line
and overwriting the 79 with a 65 each time?
Similarly and perhaps to simplify the question - the same logic could
be applied for lets say in cell a9 we have a formual =sum(a1:a8) - the
row 8 changes to lets say row 4 how do I get the program to enter the
4 without the manual route of manually changing the formula in cell
a9?
Thanks any help if a nudge in the right direction will be kindly
appreciated?
Allan Howden