0

I need to create a dynamic formula using an equation based on the value of a cell, but I am struggling to find what I need despite having done this before. For example I have 600 rows in an excel, I want to check something in the last 200 rows only. I have in D1 for example 600 which relates to the amount of rows. I need to ideally do something like so:

SUM(A(D1+1-200):A(D1+1))

In other words

SUM(A401:A601)

I have tried the following as I thought the dollar sign left this as variable, but it is not working for me.

SUM($A$(D1+1-200):$A$(D1+1))

Unfortunately all searches give me results unrelated to what I am trying to achieve.

1
  • The dollar sign changes a columns or row reference from relative to absolute. What I think you want is INDIRECT Mar 14, 2018 at 16:41

2 Answers 2

3

You can use INDEX, which is not volatile:

=SUM(INDEX(A:A,D1+1-200):INDEX(A:A,D1+1))
1

Consider:

=SUM(INDIRECT("A"&(D1+1-200)&":A"&(D1+1)))

enter image description here

Which, for the case illustrated above, is the same as:

=SUM(A1:A201)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .