nonsequential cell references
 

In a worksheet I have got a set of data where column A contains a week ending
date and then in columns B,C,D etc are the data refering to that week (so A4
is 7/10/04, B4 is 7/17/04 and so on).
Elsewhere on the spreadsheet I want to copy every fourth row from the
above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12
and so on.

Any ideas how to do it in a way that means I can easily drag a formula down,
rather than what I have done so far which is to copy every cell and then gone
through and deleted 3 weeks, miss a row and then delete the next 3 weeks data
etc?

Tim.

nonsequential cell references
 

One way ..

Put in L4: =INDIRECT("A"&ROWS($A$1:A1)*4)
Copy down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Tim" wrote in message
...
In a worksheet I have got a set of data where column A contains a week
ending
date and then in columns B,C,D etc are the data refering to that week (so
A4
is 7/10/04, B4 is 7/17/04 and so on).
Elsewhere on the spreadsheet I want to copy every fourth row from the
above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell
A12
and so on.

Any ideas how to do it in a way that means I can easily drag a formula
down,
rather than what I have done so far which is to copy every cell and then
gone
through and deleted 3 weeks, miss a row and then delete the next 3 weeks
data
etc?

Tim.

nonsequential cell references
 

Hi Tim

In cell L4
=INDIRECT("A"&(Row()-3)*4
Copy down as required

Regards

Roger Govier


Tim wrote:
In a worksheet I have got a set of data where column A contains a week ending
date and then in columns B,C,D etc are the data refering to that week (so A4
is 7/10/04, B4 is 7/17/04 and so on).
Elsewhere on the spreadsheet I want to copy every fourth row from the
above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12
and so on.

Any ideas how to do it in a way that means I can easily drag a formula down,
rather than what I have done so far which is to copy every cell and then gone
through and deleted 3 weeks, miss a row and then delete the next 3 weeks data
etc?

Tim.

nonsequential cell references
 

Thanks for your help guys ... it is a beautiful thing you have come up with
.... and will save me loads of hassle. It works a treat when I drag the
formula down. However, is it possible to modify it so that when I drag the
formula to the right the relative column references also change? (my dataset
is 10 columns wide) i.e. I need cell cell L4 to= A4 (and L5 to=A8 and so on
down ... that problem you have already solved), but then I need to drag that
across so that M4 =B4 and so on to the right. Can that be built into the
indirect formula, or do I have to drag across and then manually change the
"A" to B, C, D etc across the page?

Thanks again for help so far,

Tim.

"Roger Govier" wrote:

Hi Tim

In cell L4
=INDIRECT("A"&(Row()-3)*4
Copy down as required

Regards

Roger Govier


Tim wrote:
In a worksheet I have got a set of data where column A contains a week ending
date and then in columns B,C,D etc are the data refering to that week (so A4
is 7/10/04, B4 is 7/17/04 and so on).
Elsewhere on the spreadsheet I want to copy every fourth row from the
above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell A12
and so on.

Any ideas how to do it in a way that means I can easily drag a formula down,
rather than what I have done so far which is to copy every cell and then gone
through and deleted 3 weeks, miss a row and then delete the next 3 weeks data
etc?

Tim.

nonsequential cell references
 

Try...

L4, copied down and across:

=INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1)

Hope this helps!

In article ,
Tim wrote:

Thanks for your help guys ... it is a beautiful thing you have come up with
... and will save me loads of hassle. It works a treat when I drag the
formula down. However, is it possible to modify it so that when I drag the
formula to the right the relative column references also change? (my dataset
is 10 columns wide) i.e. I need cell cell L4 to= A4 (and L5 to=A8 and so on
down ... that problem you have already solved), but then I need to drag that
across so that M4 =B4 and so on to the right. Can that be built into the
indirect formula, or do I have to drag across and then manually change the
"A" to B, C, D etc across the page?

Thanks again for help so far,

Tim.

"Roger Govier" wrote:

Hi Tim

In cell L4
=INDIRECT("A"&(Row()-3)*4
Copy down as required

Regards

Roger Govier


Tim wrote:
In a worksheet I have got a set of data where column A contains a week
ending
date and then in columns B,C,D etc are the data refering to that week (so
A4
is 7/10/04, B4 is 7/17/04 and so on).
Elsewhere on the spreadsheet I want to copy every fourth row from the
above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell
A12
and so on.

Any ideas how to do it in a way that means I can easily drag a formula
down,
rather than what I have done so far which is to copy every cell and then
gone
through and deleted 3 weeks, miss a row and then delete the next 3 weeks
data
etc?

Tim.

nonsequential cell references
 

Domenic,

That's brilliant! Thanks for that ... it works (but I've no idea how: what
is the Index formula telling Excel to do? If anyone can explain that I'd be
even happier).

Cheers,

Tim.

"Domenic" wrote:

Try...

L4, copied down and across:

=INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1)

Hope this helps!

In article ,
Tim wrote:

Thanks for your help guys ... it is a beautiful thing you have come up with
... and will save me loads of hassle. It works a treat when I drag the
formula down. However, is it possible to modify it so that when I drag the
formula to the right the relative column references also change? (my dataset
is 10 columns wide) i.e. I need cell cell L4 to= A4 (and L5 to=A8 and so on
down ... that problem you have already solved), but then I need to drag that
across so that M4 =B4 and so on to the right. Can that be built into the
indirect formula, or do I have to drag across and then manually change the
"A" to B, C, D etc across the page?

Thanks again for help so far,

Tim.

"Roger Govier" wrote:

Hi Tim

In cell L4
=INDIRECT("A"&(Row()-3)*4
Copy down as required

Regards

Roger Govier


Tim wrote:
In a worksheet I have got a set of data where column A contains a week
ending
date and then in columns B,C,D etc are the data refering to that week (so
A4
is 7/10/04, B4 is 7/17/04 and so on).
Elsewhere on the spreadsheet I want to copy every fourth row from the
above-mentioned data set, so cell L4= cell A4, then L5= cell A8, L6= cell
A12
and so on.

Any ideas how to do it in a way that means I can easily drag a formula
down,
rather than what I have done so far which is to copy every cell and then
gone
through and deleted 3 weeks, miss a row and then delete the next 3 weeks
data
etc?

Tim.

nonsequential cell references
 

If we take a look at the following...

=INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1)

The ROWS function returns the number of rows within a specified range.
In this example...

ROWS(L$4:L4) --- returns 1

ROWS(L$4:L4)*4-4+1 --- also returns 1

This number is then used as an argument for the INDEX function and
returns the value from the first cell of the specified range, A4:A65536.

As the formula is copied/dragged to the next cell below, the formula
becomes...

=INDEX(A$4:A$65536,ROWS(L$4:L5)*4-4+1)

Here...

ROWS(L$4:L5) --- returns 2

ROWS(L$4:L5)*4-4+1 --- now returns 5

This time the value from the fifth cell of the specified range,
A4:A65536, is returned, and so on...

Hope this helps!

In article ,
Tim wrote:

Domenic,

That's brilliant! Thanks for that ... it works (but I've no idea how: what
is the Index formula telling Excel to do? If anyone can explain that I'd be
even happier).

Cheers,

Tim.

nonsequential cell references
 

Brilliant. Thanks for taking the time to explain.

Tim.

"Domenic" wrote:

If we take a look at the following...

=INDEX(A$4:A$65536,ROWS(L$4:L4)*4-4+1)

The ROWS function returns the number of rows within a specified range.
In this example...

ROWS(L$4:L4) --- returns 1

ROWS(L$4:L4)*4-4+1 --- also returns 1

This number is then used as an argument for the INDEX function and
returns the value from the first cell of the specified range, A4:A65536.

As the formula is copied/dragged to the next cell below, the formula
becomes...

=INDEX(A$4:A$65536,ROWS(L$4:L5)*4-4+1)

Here...

ROWS(L$4:L5) --- returns 2

ROWS(L$4:L5)*4-4+1 --- now returns 5

This time the value from the fifth cell of the specified range,
A4:A65536, is returned, and so on...

Hope this helps!

In article ,
Tim wrote:

Domenic,

That's brilliant! Thanks for that ... it works (but I've no idea how: what
is the Index formula telling Excel to do? If anyone can explain that I'd be
even happier).

Cheers,

Tim.