Nesting Row() inside Concatenate and Indirect

[shahp89]

I am trying to replace a manual text input with a row reference inside this formula but it isn't working.

Version with Manual Row Reference

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #33af4a}span.s5 {color: #ff9c1b}span.s6 {color: #fe4fdd}span.s7 {color: #0057d6}</style><style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #33af4a}span.s5 {color: #ff9c1b}span.s6 {color: #fe4fdd}span.s7 {color: #0057d6}</style>
{=IFERROR(INDEX('Sheet1'!$D$9:$D$38, SMALL(IF(INDIRECT(CONCATENATE("'Sheet1'!", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), "9", ":", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), "38"))>0,ROW('Sheet1'!$D$9:$D$38)-MIN(ROW('Sheet1'!$D$9:$D$38))+1),ROWS($B$95:B97))), "")}
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #33af4a}span.s5 {color: #ff9c1b}span.s6 {color: #fe4fdd}span.s7 {color: #0057d6}</style>
I refer to rows 9 and 38 manually (see bold)

I want to replace them with dynamic row references, but when I replace "9" with row('Sheet1!'D8)+1, the formula no longer yields the same result.

{=IFERROR(INDEX('Sheet1'!$D$9:$D$38, SMALL(IF(INDIRECT(CONCATENATE("'Sheet1'!", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), row('Sheet1'!D8)+1, ":", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), row('Sheet1'!D39)-1))>0,ROW('Sheet1'!$D$9:$D$38)-MIN(ROW('Sheet1'!$D$9:$D$38))+1),ROWS($B$95:B97))), "")}

I'd really appreciate help. Thank you!

 

[yky]

What do the following two formulae give you?

CONCATENATE("'Sheet1'!", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), row('Sheet1'!D8)+1, ":", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), row('Sheet1'!D39)-1)

CONCATENATE("'Sheet1'!", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), row('Sheet1'!D8)+1, ":", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), 38)

 

[shahp89]

I managed to figure it out for a reason I don't quite understand. I had to put a sum() around the row() formulas.

The following works:
{=IFERROR(INDEX('Sheet1'!$D$9:$D$38, SMALL(IF(INDIRECT(CONCATENATE("'Sheet1'!", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), sum(row('Sheet1'!D8)+1), ":", SUBSTITUTE(ADDRESS(1, MATCH($A$2, 'Sheet1'!$A$7:$AM$7, 0)), "1", ""), sum(row('Sheet1'!D39)-1)))>0,ROW('Sheet1'!$D$9:$D$38)-MIN(ROW('Sheet1'!$D$9:$D$38))+1),ROWS($B$95:B97))), "")}