Formula Reduction

[rilzniak]

I have another three formulas I'm hoping someone can review and provide feedback on:

1) Array formula: =IF($A3="","",INDEX(Inputs!$B:$S,SMALL(IF(Inputs!$B:$B=$A$1,ROW(Inputs!$B:$B)),ROW(1:1)),COLUMN(C1)))

2) Array formula: =IFERROR(INDEX(Inputs!$A:$S,SMALL(IF(Inputs!$A:$A=$A$1,ROW(Inputs!$A:$A)),ROW(1:1)),COLUMN(B1)),"")

3) =IF($A3="","",INDIRECT("'Inputs'!"&ADDRESS(MATCH($A$1,Inputs!$A:$A,0),COLUMN(C1),4)))

Suggestions on how to make any of these better?

Ryan

 

[Aladin Akyurek]

I'm not sure I follow. Can you elaborate?

What is the formula row, the first instance?

 

[rilzniak]

What is the formula row, the first instance?

The first row is just the headers for the columns. The formulas I provided were in the second row.

 

[Aladin Akyurek]

The first row is just the headers for the columns. The formulas I provided were in the second row.

You seem very much wedded to these formulas...

Now select the first row where this lovely formula is:

=IFERROR(INDEX(Inputs!$A$1:$S$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,ROW(Inputs!$A$1:$A$2000)),ROW(1:1)),COLUMN(C1)),"")

Right click, insert a row for a few times. Are you happy?

 

[rilzniak]

You seem very much wedded to these formulas...

Now select the first row where this lovely formula is:

=IFERROR(INDEX(Inputs!$A$1:$S$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,ROW(Inputs!$A$1:$A$2000)),ROW(1:1)),COLUMN(C1)),"")

Right click, insert a row for a few times. Are you happy?

I'm not sure what that accomplishes...

 

[Aladin Akyurek]

I'm not sure what that accomplishes...

You'll get get wrong results, that's what it accomplishes. Meaning the formula is not robust. Try also inserting a few columns before the current column A...

 

[rilzniak]

You'll get get wrong results, that's what it accomplishes. Meaning the formula is not robust. Try also inserting a few columns before the current column A...

Okay, I follow you now. Sorry, I'm a little slow at this. Do you have any suggestions for how I could correct that?

 

[Aladin Akyurek]

Okay, I follow you now. Sorry, I'm a little slow at this. Do you have any suggestions for how I could correct that?

What is the first cell where you put this formula?

=IFERROR(INDEX(Inputs!$A$1:$S$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,ROW(Inputs!$A$1:$A$2000)),ROW(1:1)),COLUMN(C1)),"")

And do you copy this across?

 

[rilzniak]

What is the first cell where you put this formula?

=IFERROR(INDEX(Inputs!$A$1:$S$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,ROW(Inputs!$A$1:$A$2000)),ROW(1:1)),COLUMN(C1)),"")

And do you copy this across?

The first cell is A3. Yes, I copy it across the 3rd row.

 

[markmzz]

If I understand correctly what you want, maybe this can helps:

1) =IF($A3="","",IFERROR(INDEX(Inputs!$B$1:$S$2000,SMALL(IF(Inputs!$B$1:$B$2000=$A$1,ROW(Inputs!$B$1:$B$2000)-ROW(Inputs!$B$1)+1),ROWS(B$3:B3)),COLUMNS($B3:B3)+2),""))

2) =IFERROR(INDEX(Inputs!$A$1:$S$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,ROW(Inputs!$A$1:$A$2000)-ROW(Inputs!$A$1)+1),ROWS(A$3:A3)),COLUMNS($A3:A3)+2),"")

3) =IF($A3="","",INDIRECT("'Inputs'!"&ADDRESS(MATCH($A3,Inputs!$A$1:$A$2000,0),COLUMNS($C3:C3)+2)))

Markmzz

 

[Aladin Akyurek]

The first cell is A3. Yes, I copy it across the 3rd row.

A3, control+shift+enter, not just enter, copy down, and across:

=IFERROR(INDEX(Inputs!C$1:C$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,
  ROW(Inputs!$A$1:$A$2000)-ROW(Inputs!$A$1)+1),ROWS(A$3:A3))),"")