Copying from another worksheet

[Volume]

Hi!

I would really apreciate some help...

I have a worksheet with some data. I have another worksheet where I
make a connection to the 1st worksheet the following way...

Field A1=Sheet1!A1
Field A2=Sheet1!A6
Field A3=Sheet1!A10
...

Now when I simply try to copy those formulas (expecting the references
to the other worksheet to increase by 5) i get nothing, that is, I get
an increment of 1 for the rest of the fields...

How do I force excel to increment by 5 when copying cells?

Thanks!!!!

 

[Pete]

The way I have done this in the past is to copy A1 to A6, then to A11,
then to A16 etc then delete the rows between the cells you have copied
(i.e. 2:5, 7:10 etc). If you have a lot of blank rows to delete, you
can set up a filter, select Blank then Edit | Delete Row, then remove
the filter.

Pete

 

[Gord Dibben]

You have a typo in your description.

A1 + 5 is A6

A6 + 5 should be A11, not A10

Which do you want?

Formula to return A1, A6, A11, A16

=OFFSET(Sheet1!$A$1,5*ROW()-5,0) entered in A1 of target sheet.

Drag/copy down.


Gord Dibben Excel MVP

 

[Dave Peterson]

Shouldn't A10, be A11?

You could use a formula like:
=INDEX(Sheet1!A:A,(ROW()-1)*5+1)

Or you could use this technique:
Put this in A1:
$$$$$sheet1!a1
Put this in A2:
$$$$$sheet1!a6

Select A1:A2 and drag down as far as you need it.

Select that range
edit|replace
what: $$$$$
with: =
replace all

 

[Volume]

Thanks!

You guys rock...