Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|