Copying a formula with an increase of more than 1
 

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!

:-)

Copying a formula with an increase of more than 1
 

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
---------------
Jacob Skaria


"Wombat" wrote:

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!

:-)

Copying a formula with an increase of more than 1
 

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" wrote:
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, ...

Copying a formula with an increase of more than 1
 

Awesome. Thank you very much

I love Excel when it works!

"Max" wrote:

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" wrote:
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, ...

Copying a formula with an increase of more than 1
 

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
---
"Wombat" wrote:
Awesome. Thank you very much

Copying a formula with an increase of more than 1
 

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.
--
HTH...

Jim Thomlinson


"Wombat" wrote:

Awesome. Thank you very much

I love Excel when it works!

"Max" wrote:

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" wrote:
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, ...