Way to filter out Unused space and Copy/paste onto another worksheet

I'd use INDEX(SMALL instead to only fill with data that isn't blank.

Unmerge A10:E52.

A10:

Formula:

Please Login or Register  to view this content.

Confirm this formula with Ctrl+Shift+Enter

Now merge A10:E10, and copy that cell down to A52.

You adjusted the referenced range, but not the row outputs.

The ROW(range) or COLUMN(range) has to equal the number of rows or columns in the range you're indexing.

So if you INDEX(a21:a50,.... you also need the ROW(a1:a30). The index is 30 rows, you need 30 rows to output.

=IFERROR(INDEX('Daily Chgs'!$B$3:$B$55,SMALL(IF('Daily Chgs'!$B$3:$B$55<>"",ROW($A$1:$A$43)),ROW(A1))),"")

So since the INDEX has changed to b3:b55 (53 rows) you need to adjust the output to 53.

=IFERROR(INDEX('Daily Chgs'!$B$3:$B$55,SMALL(IF('Daily Chgs'!$B$3:$B$55<>"",ROW($A$1:$A$53)),ROW(A1))),"")

That is correct!

Here's the logical breakdown, (starting with the 3rd row) in case you're interested:

=INDEX('Daily Chgs'!$B$3:$B$59,SMALL(IF('Daily Chgs'!$B$3:$B$59<>"",ROW('Daily Chgs'!$A$1:$A$57)),ROW(A3)))

The nested expression evaluates to True and False:

=INDEX('Daily Chgs'!$B$3:$B$59,SMALL(IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE},ROW('Daily Chgs'!$A$1:$A$57)),ROW(A3)))

The ROW expression evaluates next:

=INDEX('Daily Chgs'!$B$3:$B$59,SMALL(IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57}),ROW(A3)))

Each True is converted the it's associated ROW #:

=INDEX('Daily Chgs'!$B$3:$B$59,SMALL({1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE;FALSE;FALSE;25;FALSE;FALSE;FALSE;FALSE;FALSE;31;32;FALSE;FALSE;FALSE;FALSE;37;FALSE;FALSE;FALSE;FALSE;FALSE;43;FALSE;FALSE;FALSE;FALSE;FALSE;49;FALSE;FALSE;FALSE;FALSE;54;FALSE;56;57},ROW(A3)))

The Final ROW reference which has incremented from the drag down evaluates to 3, and SMALL evaluates to 3rd smallest value thereby skipping all the blank rows and grabbing #20:

=INDEX('Daily Chgs'!$B$3:$B$59,{20})

="WellGuide MWD Tools- Standby "


The output of the IF just has to have the same number of variables as the expression we evaluate, but starting at 1.

If you want, you can technically use:

=INDEX('Daily Chgs'!$B$3:$B$59,SMALL(IF('Daily Chgs'!$B$3:$B$59<>"",ROW('Daily Chgs'!$B$3:$B$59)-ROW($B$2)),ROW(A1)))

which will keep all the reference identical, in case you insert any rows at the top.