Formula Help
 

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

Formula Help
 

Jeremy;215789 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

If the data is always every 5 rows in sheet2 and you start in A1 on
sheet1 enter in A1
Code:
--------------------
=indirect("sheet2!"&"A"&row()*5)
--------------------
and pull down as needed


--
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

Formula Help
 

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

Formula Help
 

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

Formula Help
 

**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.



"Jeremy" 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