Formula from consecutive rows to alternate rows?

  • Follow


I have a worksheet with data in consecutive rows.

I have another worksheet into which I want to insert a formula on every 
second row, but referring to each consecutive row in the first worksheet.

For example, on Worksheet 1, in column A:

Row 1: 123
Row 2: 456
Row 3: 789
etc

On Worksheet 2 I want to show:

Row 1: ='Worksheet 1'!A1
Row 2: 
Row 3: ='Worksheet 2'!A2
Row 4:
Row 5: ='Worksheet 3'!A3

How do I copy the above formula down?
0
Reply Utf 3/25/2010 1:21:02 PM

In Sheet2 cell A1 copy th ebelow formula and copy down as required

=IF(MOD(ROW(),2)=1,INDEX(Sheet1!A:A,CEILING(ROW()/2,1)),"")

-- 
Jacob


"Neville Bailey" wrote:

> I have a worksheet with data in consecutive rows.
> 
> I have another worksheet into which I want to insert a formula on every 
> second row, but referring to each consecutive row in the first worksheet.
> 
> For example, on Worksheet 1, in column A:
> 
> Row 1: 123
> Row 2: 456
> Row 3: 789
> etc
> 
> On Worksheet 2 I want to show:
> 
> Row 1: ='Worksheet 1'!A1
> Row 2: 
> Row 3: ='Worksheet 2'!A2
> Row 4:
> Row 5: ='Worksheet 3'!A3
> 
> How do I copy the above formula down?
0
Reply Utf 3/25/2010 1:33:01 PM

Thank you Jacob - that worked perfectly!

"Jacob Skaria" wrote:

> In Sheet2 cell A1 copy th ebelow formula and copy down as required
> 
> =IF(MOD(ROW(),2)=1,INDEX(Sheet1!A:A,CEILING(ROW()/2,1)),"")
> 
> -- 
> Jacob
> 
> 
> "Neville Bailey" wrote:
> 
> > I have a worksheet with data in consecutive rows.
> > 
> > I have another worksheet into which I want to insert a formula on every 
> > second row, but referring to each consecutive row in the first worksheet.
> > 
> > For example, on Worksheet 1, in column A:
> > 
> > Row 1: 123
> > Row 2: 456
> > Row 3: 789
> > etc
> > 
> > On Worksheet 2 I want to show:
> > 
> > Row 1: ='Worksheet 1'!A1
> > Row 2: 
> > Row 3: ='Worksheet 2'!A2
> > Row 4:
> > Row 5: ='Worksheet 3'!A3
> > 
> > How do I copy the above formula down?
0
Reply Utf 3/25/2010 1:58:01 PM

2 Replies
1200 Views

(page loaded in 1.062 seconds)


Reply: