Range look up based on number in cell

Hi,

Is there a way to change the range of a formula based on a number entered in a cell?
I.e
If my sum is looking up d1 to d7 It is because with two other cells the numbers 1 and 7 are entered.

That is possible.

Create range by using address function and then use indirect function.

Regards,
Suhas

ive never used an address formula, could you show me an example

if i have a 1000 rows of data and i enter 1 in a cell and 100 in another cell, i only want the range to look up 1 - 100 rows.

=SUM(OFFSET(A1,0,0,6,1))

this will sum from A1 to A7
or =SUM(OFFSET(A1,b1-1,0,b2-1,1)) ' where b1 and b2 are from and to row numbers

Hi Sadath,

I would have to edit to formula to change the look up, can i enter a number in a different cell to change the lookup?

Write below formula
=SUM(INDIRECT("A"&C1&":"&"A"&D1))
C1= First row
D1= last row

Regards,
Suhas

Excellent thank you Pangam