If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Formula from consecutive rows to alternate rows?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Formula from consecutive rows to alternate rows?

#1
March 25th 10, 01:21 PM posted to microsoft.public.excel.misc
 Neville Bailey[_2_] external usenet poster Posts: 7
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
March 25th 10, 01:33 PM posted to microsoft.public.excel.misc
 Jacob Skaria external usenet poster Posts: 8,521
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
March 25th 10, 01:58 PM posted to microsoft.public.excel.misc
 Neville Bailey[_2_] external usenet poster Posts: 7
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?

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 07:49 AM.