Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 10th 09, 03:36 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 148
Default Copy lines from sheet 2 to sheet 6

I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4, etc
thanks

  #2   Report Post  
Old May 10th 09, 09:56 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default Copy lines from sheet 2 to sheet 6

On Sheet6, enter this in B1:
=Sheet2!I1

And in B2 enter this:
=INDEX(Sheet2!I:I,7*ROWS($1:1))
and copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pcor" wrote in message
...
I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4, etc
thanks


  #3   Report Post  
Old May 11th 09, 03:25 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 148
Default Copy lines from sheet 2 to sheet 6

Thanks. It worked very well. BUT...will you please exxplain what that is
REALLY doing. I think I understand all but the ROWS($!:1) got me confused.
Thansk again


"Ragdyer" wrote:

On Sheet6, enter this in B1:
=Sheet2!I1

And in B2 enter this:
=INDEX(Sheet2!I:I,7*ROWS($1:1))
and copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pcor" wrote in message
...
I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4, etc
thanks



  #4   Report Post  
Old May 11th 09, 07:14 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 1
Default Copy lines from sheet 2 to sheet 6


Hi,

the ROWS function is well-documented in XL and... Google is your friend
!

pcor;340564 Wrote:
Thanks. It worked very well. BUT...will you please exxplain what that
is
REALLY doing. I think I understand all but the ROWS($!:1) got me
confused.
Thansk again


"Ragdyer" wrote:

On Sheet6, enter this in B1:
=Sheet2!I1

And in B2 enter this:
=INDEX(Sheet2!I:I,7*ROWS($1:1))
and copy down as needed.
--
HTH,

RD


---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may

benefit !

---------------------------------------------------------------------------
"pcor" wrote in message
...
I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row

1,2,3,4, etc
thanks





--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95134

  #5   Report Post  
Old May 11th 09, 05:25 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default Copy lines from sheet 2 to sheet 6

The Index() function has different forms.

The one used here can be described as "referencing" a location.

Since it's a one dimension reference (a single column), the second argument
refers to a row within that column.

You could just as easily have used formulas such as these:
=INDEX(Sheet2!I:I,1)
=INDEX(Sheet2!I:I,2)
=INDEX(Sheet2!I:I,3)
.... etc.

If you indexed a single row:

=INDEX(Sheet2!3:3,1)
=INDEX(Sheet2!3:3,2)
=INDEX(Sheet2!3:3,3)

That second argument would refer to a column within that row.

You should note however, that these row (column) references (1, 2, 3 ...
etc.) *do not* refer to the *Sheet* rows (columns), but to the cells
*strictly within* the indexed column or row.

=INDEX(Sheet2!I20:I30,1)
refers to I20, that being the *first* row of the indexed (chosen) range.

Therefo
=INDEX(Sheet2!I20:I30,4)
refers to I23, the 4th row of the indexed range.

=INDEX(Sheet2!F3:M3,4)
refers to I3, the 4th column of the indexed range.

However, It would be very tedious to have to manually change the numbers in
the 2nd argument as we copied the formula down or across.

There are a couple of nice functions which count the number of rows and
columns within their parameters.
They return a simple number which we can use to increment automatically.

=Rows(1:1)
=Columns(A:A)

As you copy the Rows() down, and the Columns() across they increase.
=Rows(1:1)
=Rows(2:2)
=Rows(3:3)
BUT, they still equal 1, the number of rows within it's parameters.
SO, we just anchor the first reference by making it absolute, and only allow
the second to increase:

=Rows($1:1)
=Rows($1:2)
=Rows($1:3)

And this returns a number that automatically increments as it's copied.

As an aside, you can anchor the second reference and make the function
*decrement* as it's copied.
=Rows(1:$10)
=Rows(2:$10)
=Rows(3:$10)

Finally, since you wanted your rows in multiples of 7, we simply multiplied
the returns of the Rows() function by 7.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"pcor" wrote in message
...
Thanks. It worked very well. BUT...will you please exxplain what that is
REALLY doing. I think I understand all but the ROWS($!:1) got me confused.
Thansk again


"Ragdyer" wrote:

On Sheet6, enter this in B1:
=Sheet2!I1

And in B2 enter this:
=INDEX(Sheet2!I:I,7*ROWS($1:1))
and copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pcor" wrote in message
...
I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4,
etc
thanks







  #6   Report Post  
Old May 12th 09, 09:41 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 148
Default Copy lines from sheet 2 to sheet 6

That was a G R E A T explanation. Many Thanks

"RagDyeR" wrote:

The Index() function has different forms.

The one used here can be described as "referencing" a location.

Since it's a one dimension reference (a single column), the second argument
refers to a row within that column.

You could just as easily have used formulas such as these:
=INDEX(Sheet2!I:I,1)
=INDEX(Sheet2!I:I,2)
=INDEX(Sheet2!I:I,3)
.... etc.

If you indexed a single row:

=INDEX(Sheet2!3:3,1)
=INDEX(Sheet2!3:3,2)
=INDEX(Sheet2!3:3,3)

That second argument would refer to a column within that row.

You should note however, that these row (column) references (1, 2, 3 ...
etc.) *do not* refer to the *Sheet* rows (columns), but to the cells
*strictly within* the indexed column or row.

=INDEX(Sheet2!I20:I30,1)
refers to I20, that being the *first* row of the indexed (chosen) range.

Therefo
=INDEX(Sheet2!I20:I30,4)
refers to I23, the 4th row of the indexed range.

=INDEX(Sheet2!F3:M3,4)
refers to I3, the 4th column of the indexed range.

However, It would be very tedious to have to manually change the numbers in
the 2nd argument as we copied the formula down or across.

There are a couple of nice functions which count the number of rows and
columns within their parameters.
They return a simple number which we can use to increment automatically.

=Rows(1:1)
=Columns(A:A)

As you copy the Rows() down, and the Columns() across they increase.
=Rows(1:1)
=Rows(2:2)
=Rows(3:3)
BUT, they still equal 1, the number of rows within it's parameters.
SO, we just anchor the first reference by making it absolute, and only allow
the second to increase:

=Rows($1:1)
=Rows($1:2)
=Rows($1:3)

And this returns a number that automatically increments as it's copied.

As an aside, you can anchor the second reference and make the function
*decrement* as it's copied.
=Rows(1:$10)
=Rows(2:$10)
=Rows(3:$10)

Finally, since you wanted your rows in multiples of 7, we simply multiplied
the returns of the Rows() function by 7.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"pcor" wrote in message
...
Thanks. It worked very well. BUT...will you please exxplain what that is
REALLY doing. I think I understand all but the ROWS($!:1) got me confused.
Thansk again


"Ragdyer" wrote:

On Sheet6, enter this in B1:
=Sheet2!I1

And in B2 enter this:
=INDEX(Sheet2!I:I,7*ROWS($1:1))
and copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pcor" wrote in message
...
I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4,
etc
thanks






  #7   Report Post  
Old May 13th 09, 04:38 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default Copy lines from sheet 2 to sheet 6

You're welcome, and thank you for feeding back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"pcor" wrote in message
...
That was a G R E A T explanation. Many Thanks

"RagDyeR" wrote:

The Index() function has different forms.

The one used here can be described as "referencing" a location.

Since it's a one dimension reference (a single column), the second
argument
refers to a row within that column.

You could just as easily have used formulas such as these:
=INDEX(Sheet2!I:I,1)
=INDEX(Sheet2!I:I,2)
=INDEX(Sheet2!I:I,3)
.... etc.

If you indexed a single row:

=INDEX(Sheet2!3:3,1)
=INDEX(Sheet2!3:3,2)
=INDEX(Sheet2!3:3,3)

That second argument would refer to a column within that row.

You should note however, that these row (column) references (1, 2, 3 ...
etc.) *do not* refer to the *Sheet* rows (columns), but to the cells
*strictly within* the indexed column or row.

=INDEX(Sheet2!I20:I30,1)
refers to I20, that being the *first* row of the indexed (chosen) range.

Therefo
=INDEX(Sheet2!I20:I30,4)
refers to I23, the 4th row of the indexed range.

=INDEX(Sheet2!F3:M3,4)
refers to I3, the 4th column of the indexed range.

However, It would be very tedious to have to manually change the numbers
in
the 2nd argument as we copied the formula down or across.

There are a couple of nice functions which count the number of rows and
columns within their parameters.
They return a simple number which we can use to increment automatically.

=Rows(1:1)
=Columns(A:A)

As you copy the Rows() down, and the Columns() across they increase.
=Rows(1:1)
=Rows(2:2)
=Rows(3:3)
BUT, they still equal 1, the number of rows within it's parameters.
SO, we just anchor the first reference by making it absolute, and only
allow
the second to increase:

=Rows($1:1)
=Rows($1:2)
=Rows($1:3)

And this returns a number that automatically increments as it's copied.

As an aside, you can anchor the second reference and make the function
*decrement* as it's copied.
=Rows(1:$10)
=Rows(2:$10)
=Rows(3:$10)

Finally, since you wanted your rows in multiples of 7, we simply
multiplied
the returns of the Rows() function by 7.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"pcor" wrote in message
...
Thanks. It worked very well. BUT...will you please exxplain what that is
REALLY doing. I think I understand all but the ROWS($!:1) got me confused.
Thansk again


"Ragdyer" wrote:

On Sheet6, enter this in B1:
=Sheet2!I1

And in B2 enter this:
=INDEX(Sheet2!I:I,7*ROWS($1:1))
and copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"pcor" wrote in message
...
I have some data in sheet 2 in col I.
the data I want to capture is spaced by 7 spaces all the ay down:
EX:
I have data in sheet 2 col I row 1 ,7,14,21 etc

I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4,
etc
thanks










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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate sheet with lines from another sheet Texhun Excel Discussion (Misc queries) 0 April 14th 09 09:06 PM
Populate sheet with lines from other sheet Texhun Excel Discussion (Misc queries) 0 April 2nd 09 09:56 PM
Auto Copy/autofill Text from sheet to sheet if meets criteria Joyce Excel Discussion (Misc queries) 0 November 20th 08 11:05 PM
Search for rows in one sheet and copy into another sheet based on customer id [email protected] Excel Worksheet Functions 1 October 22nd 07 03:09 AM
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. RonMc5 Excel Discussion (Misc queries) 9 February 3rd 05 12:51 AM


All times are GMT +1. The time now is 12:46 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017