Copying a formula with an increase of more than 1


W

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!

:)
 
Ad

Advertisements

J

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
 
W

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
 
M

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
 
Ad

Advertisements

J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top