#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 184
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"