Copying from another worksheet

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

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

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


On Fri, 16 Dec 2005 05:25:36 -0600, Volume
<Volume.204rny_1134732600.8683@excelforum-nospam.com> wrote:

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

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 wrote:
>
> 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!!!!
>
> --
> Volume
> ------------------------------------------------------------------------
> Volume's Profile: http://www.excelforum.com/member.php...o&userid=29693
> View this thread: http://www.excelforum.com/showthread...hreadid=494090

--

Dave Peterson

Thanks!

You guys rock...