# Excel Cell reference from equation

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.

• The dollar sign changes a columns or row reference from relative to absolute. What I think you want is `INDIRECT` – cybernetic.nomad Mar 14 '18 at 16:41

You can use INDEX, which is not volatile:

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

Consider:

``````=SUM(INDIRECT("A"&(D1+1-200)&":A"&(D1+1)))
`````` Which, for the case illustrated above, is the same as:

``````=SUM(A1:A201)
``````