Copying a formula with an increase of more than 1

[Wombat]

I have a list in an excel worksheet with the data going down it one row at a
time (eg. row 1= data 1; row 2= data 2)

I want to link these cells to another worksheet but the data needs to be
spread out with 4 rows in between each entry (row 1= data 1; row 5= data 2;
row 9 = data 3 ...)

My problems lies in not being able to copy the formula down the page as it
is adjusted automatically according to the number of rows between the new
target cell and the cell from which I have copied the formula from. I have
tried entering several of the formulae with the correct increase and then
tried to drag the formula down the page but the answers I get are as wrong as
they are frustrating.

Please help!

 

[Pecoflyer]

Supposing your original sheet is Sheet1 and the new sheet is sheet2 an
you start in A1 try the following in sheet 2

=IF(MOD(ROW();5)=1;INDIRECT("sheet1!$a"&QUOTIENT(ROW();5)+1);""

--
Pecoflye

Cheers -

Firefox 3.5.3 is as slow as possible

 

[Jacob Skaria]

Suppose you have the data in ColA of the first worksheet from Row1.

In second worksheet Row 1 paste the below formula and copy down as required

=IF(MOD(ROW()-1,4)=0,INDIRECT("'Sheet1'!A"& (ROW()-1)/4+1),"")

If this post helps click Yes

 

[Wombat]

Thank you for both your suggestions and your quick replies. I have tried
integrating your formulas into my spreadsheet but have remained
unsuccessful... I cant quite get my head around how they're supposed to
work!! Could you maybe update your formulas if I give you some more
informations?

The first sheet from which the information should be taken is called "base"
and the data goes from B55 to B138 (again, with one data value (format: text)
per row)

The linked cells should start on the second sheet in A6 and the next data
value should be in A10, A14, ...

Many thanks for your help

 

[Max]

In any startcell in Sheet2,
say in A6 (that's where you want to start):
=IF(MOD(ROWS($1:1)-1,4)=0,OFFSET(Sheet1!$B$55,INT((ROWS($1:1)-1)/4),),"")
Copy down as far as required

Easily adapt the expression to suit for other similar situations:
a. Sheet1!$B$55 is the OFFSET's anchor, ie the startcell of the source data
b. The "4" within the MOD and INT bits is the 4 rows interval that you want
The expression avoids using the row sensitive ROW() via using ROWS($1:1)
instead to always start it at 1, irrespective of the startcell that it could
be placed in.
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Wombat]

Awesome. Thank you very much

I love Excel when it works!

 

[Max]

Welcome. Do spare a moment to hit the YES button (like the one below) in that
response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Jim Thomlinson]

Note that the posted formulas use either Indirect or Offset. While these
formulas will work they are what is refered to as volatile functions. That
means that they are recalculated every time the XL application runs a
calculation. If you have a large number of these formulas you will notice a
performance hit on calculation time.

 

[Pecoflyer]

Well, of course if you don't give all the needed info,it's quit
difficult to answer...

Thank you for both your suggestions and your quick replies. I hav
tried
integrating your formulas into my spreadsheet but have remained
unsuccessful... I cant quite get my head around how they're suppose
to
work!! Could you maybe update your formulas if I give you some more
informations?

The first sheet from which the information should be taken is calle
"base"
and the data goes from B55 to B138 (again, with one data value (format
text)
per row)

The linked cells should start on the second sheet in A6 and the nex
data
value should be in A10, A14, ...

Many thanks for your help





- The Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=136072)

--
Pecoflye

Cheers -

Firefox 3.5.3 is as slow as possible