Dynamic Formula (repeating the formula)

[papare]

I would like to repeat a formula and make its dynamic depending on the request.
The formular that I would like to repeat is:

=IF(ISERROR(INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3)),"",INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3))

Looking forward to your assistance

 

[cyrilbrd]

Hi,
what version of excel are you using?
what dynamism are you requiring?

 

[StephenCrump]

Some questions/comments, in no particular order:

- Are you using Excel 2007 or later? If so, you can use the much simpler IFERROR(YourFormula,"") construction, i.e. rather than IF(ISERROR(YourFormula),"",YourFormula)

- I'm not clear why are you using Row(985:985) rather than a simple 985?

- It looks like your formula returns a value from Data!$A$2:$G$14997 based on the 985th occurrence of the value in $D$2?

You can easily make the 985 dynamic, e.g. by referring to a cell where you can vary this value.

So my guess is that your question distils down to wanting to make Data!$A$2:$G$14997 dynamic?

Assuming the data will always be anchored in A2, and that A1 is a header, you could, for example, define:

NoRows =COUNTA(Data!A:A) - 1 (Assuming data has no blanks in Column A)
NoCols = 7 (say)

then using Name Manager, define:

MyRange =OFFSET(INDIRECT("Data!A2"),,,NoRows,NoCols)

Your formula can then refer to MyRange instead of Data!$A$2:$G$14997