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.
INDIRECT