Dynamic cell referencing

[Istvan]

Usualy we make a sum like this: =sum(a5:a100)

If I want to change the end of the range that is from a100 to a50
have to change the formula like this =sum(a5:a50)

Well, what to do if I want to use a content of a different cell t
tell that the end of the range is a50? In this cell I insert a value o
50.
How to rewright the formula =sum(a5:a100) in order to get the new su
automatically if I modify a cell containing the end value for th
range.

We might call it "dynamic referencing".
Any clue?

Thanks

 

[Bob Phillips]

Istvan,

I think you mean indirect

=SUM(INDIRECT("A1:A"&A50))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Hi

2 ways to handle this

1. Enter end row number into some cell (p.e. F1)
F1=50
The formula will be
=SUM(INDIRECT("A5:A"&F1))

2. Enter the number of rows to be summed into some cell (F1)
The formula will be
=OFFSET(A5,,,F1,1)

 

[Istvan]

Hi Bob & Arvi,

YOU ARE BOTH GREAT!

With my colloges we were shocked to see these elegant and very fas
solutions.
I would like to express my gratitude to YOU.

Many thanks really.

Istva

 

[Bob Phillips]

Thanks Istvan. On behalf of Arvi and myself, we are pleased to help you and
your colleagues.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Arvi Laanemets]

Thanks for response.

Btw. my second formula wasn't completed - I forgot sum(), but from your post
follows, that you mastered it anyway.

Arvi Laanemets