Formula Help

[Jeremy]

I have a spreadsheet with 2 work books that I am trying to carry the data
from sheet 2 to sheet 1. Below is an example. This needs to be automated
because there are about 200 rows.

Thanks you



Sheet 2
A
1
2
3
4
5 Frank
6
7
8
9
10 Bob
11
12
13
14
15 Sam
16
17
18
19
20 Henry


Sheet 1 would look like with formula
A
1 Frank
2 Bob
3 Sam
4 Henry

 

[Jeremy]

What do you mean by Ctrl+Shift+Enter?

All Excel versions:

=IF(ISERR(SMALL(IF(Sheet2!A1:A200<>"",ROW(INDIRECT("1:"&ROWS(1:200)))),ROWS($1:1))),"",INDEX(Sheet2!A1:A200,SMALL(IF(Sheet2!A1:A200<>"",ROW(INDIRECT("1:"&ROWS(1:200)))),ROWS($1:1))))

Excel 2007 only:

=IFERROR(INDEX(Sheet2!A1:A200,SMALL(IF(Sheet2!A1:A200<>"",ROW(INDIRECT("1:"&ROWS(1:200)))),ROWS($1:1))),"")

Both formula above are required with Ctrl+Shift+Enter, not just Enter
copy down as far as needed

 

[Teethless mama]

**Array formula** are required with Ctrl+Shift+Enter by pressing with those
keys.
It will put curly bracket around the formula. Don't enter those curly
bracket yourself.