P

#### pcor

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

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

P

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

R

=Sheet2!I1

And in B2 enter this:

=INDEX(Sheet2!I:I,7*ROWS($1:1))

and copy down as needed.

P

REALLY doing. I think I understand all but the ROWS($!:1) got me confused.

Thansk again

R

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.

Therefore:

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

=====================================================

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

P

RagDyeR said:

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.

Therefore:

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

=====================================================

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

R

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

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.