Reply
 
LinkBack Thread Tools Display Modes
  #1   Report Post  
Old March 25th 10, 01:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 7
Default Formula from consecutive rows to alternate rows?

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?

  #2   Report Post  
Old March 25th 10, 01:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Formula from consecutive rows to alternate rows?

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?

  #3   Report Post  
Old March 25th 10, 01:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 7
Default Formula from consecutive rows to alternate rows?

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?



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Color alternate rows when after hiding selected rows Monk[_2_] Excel Worksheet Functions 6 June 7th 08 01:36 AM
Transposing data from consecutive rows into non-consecutive rows econ Excel Discussion (Misc queries) 0 March 10th 08 07:24 PM
sum alternate rows anand Excel Worksheet Functions 4 June 19th 07 07:59 AM
Add alternate rows Teri Excel Worksheet Functions 6 March 16th 05 10:15 PM
How to sum-up alternate rows ? lolex Excel Worksheet Functions 3 November 5th 04 11:57 AM


All times are GMT +1. The time now is 12:05 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017