Jeremy;215866 Wrote:
What do you mean by Ctrl+Shift+Enter?

"Teethless mama" wrote:

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:A 200<"",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


"Jeremy" wrote:

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


When the formula is entered go to the formula bar and press the three
keys at once. Brackets will be put around the formula.
This should be done each time you edit the formula


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59280