Way to have Array Formula automatically adjust when rows added

**bdouglas1011** · 09-28-2014, 02:49 PM

I have an array formula that only brings over rows that are not Blank It works OK until you add rows to the other sheet.

Please Login or Register  to view this content.

when rows are added the the Daily Chgs the Formula will adjust say i add 10 rows then $B$3:$B$45 Becomes $B$3:$B$55 But the rest ROW($A$1:$A$43) does not change
It should be ROW($A$1:$A$53) -

I'm Not sure how to modify this to adjust properly When it does not adjust properly it displays no information in cell.

Maybe there is another way to accomplish this?

**mikerickson** · 09-28-2014, 03:34 PM

You could use a dynamic named range

Name: myColumn RefersTo: ='Daily Chgs'!$B:$B
Name: myRange RefersTo: =INDEX(myColumn, 3, 1):INDEX(myColumn, COUNTA(myColumn), 1)

Then your formula would become
=IFERROR(INDEX(myColumn,SMALL(IF(myRange<>"",ROW(myRange)),ROW(A1))),"")

You may need to subtract from the COUNTA() to account for headers in B1 and B2

Alternatly, you could use, if you anticipate blank data rows and all the entries in col B to be text.
Name: myRange RefersTo: =INDEX(myColumn, 3, 1):INDEX(myColumn, Match("zzzzz", myColumn),1)

**martindwilson** · 09-28-2014, 04:00 PM

if you change to and manually add rows
=IFERROR(INDEX('Daily Chgs'!$B$3:$B$56,SMALL(IF('Daily Chgs'!$B$3:$B$56<>"",ROW('Daily Chgs'!$A$1:$A$56)),ROW(A1))),"")
then a1:a56 changes