nonsequential cell references

[Guest]

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.

 

[Max]

One way ..

Put in L4: =INDIRECT("A"&ROWS($A$1:A1)*4)
Copy down

 

[Roger Govier]

Hi Tim

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

Regards

Roger Govier

 

[Guest]

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.

 

[Domenic]

Try...

L4, copied down and across:

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

Hope this helps!

 

[Guest]

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]

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!

 

[Guest]

Brilliant. Thanks for taking the time to explain.

Tim.