#### 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?
```
 0
Utf
3/25/2010 1:21:02 PM
excel.misc 78881 articles. 0 followers.

2 Replies
1444 Views

Similar Articles

[PageSpeed] 36

```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
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
Utf
3/25/2010 1:58:01 PM

Similar Artilces:

Formula help required
I have 3 conditions score divided into 3 parts 50, 75 and 100 % If score in cell = > 27 then -3 If score in cell = >26<36 = -2 If score in cell = >18<27 = -1 My formula =IF(F48>27,"-3",IF(F48>=26<36,"-2",IF(F48<=18<27,"-1"))) If score in cell is > 27 shows false Please advise Cordially Beena K Process Analst Your conditions are a bit ambigous, shouldn't the first criteria be >=37 ? Conditions for the formula below: F48 >=37 result -3 F48 between 26 and 36 result -2 F48 between 18 and 25 result -...

Newbie - Unable to do Simple Formula(s)
Hello, Many thanks in advance! I know very little about Excel....but here i what I thought was a simple exercise: I wanted to sell some stock. Since the stock isn't doing too well righ now, I wanted to compare what I paid for it (ESPP) in the past with ho it is doing today - then figure out if I'd be losing money (and ho much). Since the values fluctuate over time (Stock price), I though "Hmm...what a good way to learn Excel" :) Essentially, all I am trying to do is figure out my loss if I were t sell today, at \$84.39. When trying to subtract an amount from today' price...

3 Formula Questions
Hi, I have three Excel 97 formula questions: 1) Suppose I have a formula which yields a dollar value with a theoretically infinite amount of decimal places, but I use column formatting to limit the decimal places to two positions. For example, the formula: =1/3 yields \$0.33333333333333333333333333333 but I limit it to two places with column formatting: \$0.33 But I then am multiplying the above results times a fixed quantity. But I need to multiply the fixed quantity times the two-place value, not the infinite place value. For example, I might need "5 times \$0.33", NOT "5 ...

how do i get missing rows back
My excel worksheet is missing rows 37-58. There is a heavy gray gridline in between rows 36 & 59. Where did they go? How do I get them back? Hi Select from rows 36:59>right click>Unhide -- Regards Roger Govier "dkresch" <dkresch@discussions.microsoft.com> wrote in message news:0EDE73AB-87DB-43DB-B9DB-E7E82C7D39D9@microsoft.com... > My excel worksheet is missing rows 37-58. There is a heavy gray gridline > in > between rows 36 & 59. Where did they go? How do I get them back? "dkresch" <dkresch@discussions.microsoft.com> wro...

Collecting data from more than one row in a pivot table
I'm making a customer base where I want to make a pivottable that ca give an statistical overview on when it is most likely to get throug to a particular type of professional (it-director, sales manager, CEO etc.). In each company I have made room for up to four contact persons, eac with his/her own sets of columns (name, position, telephone, etc) an up to three contacts so I can see the development in the contact (e.g first contact results in a call-back, the second a reference to contac person 2, the third a meeting, etc). Each time I choose the result of a particular contact from a...

I am trying to divide 2 cells (M1/L1) and L1 contains a formula that adds minutes together (I have the L1 cell formatted for minutes [m] - if that has anything to do with it). When it divides it comes up with a really wacky #. How do I get the cell to recognize the value, not the formula? Heather ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like this =M1/(L1*1440) -- Regards, Peo Sjoblom "hgraning" <hgraning.wp1hb@excelforum-nosp...

Lookup formula
Hello Is it possible to find the minimum Price for each Part and then display the Company that sells this part? A B C D E F Part Company A Company B Company C Min Price Company Part 1 \$23.99 \$21.98 \$27.55 Part 2 \$87.42 \$95.34 \$72.63 Part 2 \$53.76 \$62.51 \$61.87 Thanks Ruan Hi! To find the minimum (lowest) price: =MIN(B2:D2) To find the company: =INDEX(B1:D1,1,MATCH(MIN(B2:D2),B2:D2,0)) Note: if there i...

Having difficulty understanding SMALL function in formula
I have the following in a spreadsheet: A B C D E F G H row 1 1.1 1 76 71 #N/A 71 5 row 2 1.2 1 124 117 #N/A 117 7 row 3 1.3 1 156 159 159 159 3 row 4 2.1 2 12 5 #N/A 5 7 row 5 2.2 2 54 59 59 59 5 row 6 3.1 3 41 45 45 45 4 row 7 3.2 3 252 310 310 310 5 row 8 3.3 3 305 438 310 310 5 row 9 3.4 3 422 438 438 3 row 10 3.5 3 441 #N/A 438 3 row 11 4.1 4 33 35 35 35 2 row 12 4.2 4 107 111 111 111 4 row 13 4.3 4 180 175 #N/A 175 5 row 14 5.1 5 227 230 230 230 3 My array formula in column H is as follows: ={SMALL((\$C\$1:\$C\$14=C1)*ABS(G1-(\$D\$1:\$D\$14)),COUNTA(\$C\$1:\$C\$14)- COUNTIF(\$C\$1:\$C\$14,C1)+1)} ...

Help with difficult formula requirement
I have data with two columns on Sheet A as shown below. I need a formula in column A of sheet B to display column A of sheet 1 the number of times as indicated in column B of Sheet 1. Zero values should be excluded. I gave an example of the output I need below Sheet A 100 0 101 5 102 3 103 0 =============== Sheet B (Example of output I need) 101 is 5 times and 102 is 3 times. I need a formula on this sheet, and can not use VBA. 101 101 101 101 101 102 102 102 Hi, Assume that the data is in range C18:D21. ...

Help for formula.
Hi. I want to solve a linear of n X n equations system in Excel, where the solution can be derived as follow: Xn = ao Xn-1 = a1 * Xn Xn-2 = a2 * Xn-1 + a3 * Xn Xn-3 = a4* Xn-2 + a5 * Xn-1 + a6 * Xn e.g. The an coefficients (a0,a1,a2,….an) are all known. Lets simplify the above system using an example. Lets say that n=5 and: X5=7 X4=2*X5 X3=3*X4+2*X5 X2=1*X3+3*X4+4*X5 X1=1*x2+1*x3+1*x4+2*x5 (Then the solution is: X1=210, X2=126, X3=56, X4=14 and X5=7) Any idea how can I solve these n x n equations in general form in Excel? (I tried with sumproduct() but I stuck in coeff...

copy selected rows to second worksheet (NOT Cut + Paste)
I am trying to create a simple tool log that also incorporates a sign in/out sheet as a second worksheet. What I want is to be able to select (not using cut + paste) several rows and by simply being selected on "tool list" worksheet, temporarly copied into "sign_in" and "sign_out" worksheets. I need the data selected from sheet 1 "tools list" to fill rows (starting at 20) of the next 2 sheets, and then end user simply prints needed sheet, for employee to sign. This allows me to select only the tools that that employee is checking in/out ...

Formula as text #2
Without changing any of the options to reflect the formula in a worksheet, though an Excel worksheet function is it possible to return the formula in another cell. E.g. Cell A1=sum(a2:a200) The value returned and represented in the worksheet is say 2000 I want to return the text "=sum(a2:a200)" through a formula and not VBA. Any ideas or suggestions? Thanks in advance I suppose I could create a custom function for this but I want to avoid this if it can be done through Excel functions. "Geoff" <gh@bob.com> wrote in message news:OrXu7WeYFHA.4032@tk2msftng...

Formula inn excel
Hi, anyone know how too make a formula inn excel that can find how many days there are left too "for exsample when people turn 50 years from their birth? Try: ="Will be "&DATEDIF(A1,TODAY(),"y")+1& "in "& IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY()) &" Days time" or with a bit more text: =IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=TODAY(), "Birthday today!","Will be "&DATEDIF(A1,TODAY(),"y")+1&...

Quick Excel formula question before I go on holiday tomorrow!
I would like to keep track of my credit card expenditure while away on holiday Florida. I was thinking of an Excel spreadsheet I could keep on my PDA - something along the lines of; A B C 1 AVAILABLE 2000 UKP 3600 USD 2 3 PARKING 70 ??? 4 HOTEL ??? 250 5-20 etc. etc. 21 SPENT 208 376 22 BALANCE 1792 3224 I have taken the conversion rate in the example above from...

formula patterns with a gap of two
"Mr L Hibbert" wrote: hello im trying to compile a small folder consisting of different worksheets, each worksheet is a different customers sales etc.... with totals for JAN, FEB etc throught to the year, these totals for each customer month are to be summarised in a summary spreadsheet, how do i extract the data from the customer folder to the summary box. at the moment i can only a = formula, but copy and paste does not allow the boxes to use the formula for all the sheets, this is hard to explain For example A - summary B - customer 1, detailed sal...

Insert Formula and Copy to other cells
Hello, I need help fast! I need to be able to insert a Formula into rows that contain certain text. I then need to be able to copy all this formula across 15 columns and down to the end of the active workbook for each row that contains this criteria. I want the formula to automatically update to the correct cell reference. My formula is rather lengthy but it works perfectly in a 'normal' spreadsheet without running a macro. This is what I have: Dim intx as Integer Dim lngrow as long ActiveCell.SpecialCells(xlLastCell).Select lngrow = ActiveCell.Row 'lastcell in spreadsheet ...

Rank scores formula
Hi to all [Apologies if this post has already been attended to. After posting the question, I could not download any headers and hence could not see if my question was indeed posted (or answered). I tried twice so it might have appeared twice! Then due to a glitch at the service providers, all postings from Saturday afternoon till Monday morning were lost including my post and possible responses. So please could somebody help me. If there were any responses, please repost or send to me (jbircher@iafrica.com)] If not seen before, here is the original query: I would really like to en...

Formula question #2
how do i make a formula so that a specific cells continuosly accumulates every time another amount on a different cell is entered. eg: on a payroll sheet, the year to date field What do you want to accumulate the number of entries [use the =Count(range) or =counta(range)function], or the value of the entries [use the =sum(range( function)] ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi zerep, Check here for info: http://www.mcgimpsey.com/excel/accumulator B...

I am currenly playing a game of Ars Magica. I am using a spreadsheet to compute the info for my character. I am having a probllem with the casting total for spells related to a paticular character. The Formula is as follows : =IF(H13="","--",IF(J13="",INDIRECT(H13),MIN(INDIRECT(H13),INDIRECT(LEFT(J13,2)),INDIRECT(RIGHT(J13,2))))+IF(K13="",INDIRECT(I13),MIN(INDIRECT(I13),INDIRECT(LEFT(K13,2)),INDIRECT(RIGHT(K13,2))))+Sta+IF(N13="",0,\$H\$2)) I am trying to compute the casting total for the spells castable by this character. The total for the ...

Finding max row containing data...
Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan Hi! One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<>"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" <Dan@discussions.m...

Excel should provide an easy way to switch a column into a row, n.
A very useful feature for Excel would be the ability to easily switch a column of data into a row. This would allow a column of entries to be used as a row of headers, for example. Excel already allows switching a column into a row in the charting area, but not in the spreadsheet. One can work around this by exporting the column into Word, changing the delimiter between fields from a paragraph mark (normally hidden) to a comma, then re-importing the data into Excel - which will then be as a row. This would also allow Access to import the former column into a row, which could then be...

If Function formula
IF cell A1 contains the word "DAM" in any part of the cell then cell A must equal "UNAVAILABLE" the key here is that the cell needs to contain the word "DAM" and if i does i need it to be identified in a seperate cell. Any suggestions?? Many Thank -- Message posted from http://www.ExcelForum.com Hi try =IF(ISNUMBER(FIND("DAM",A1)),"Unavailable","") >-----Original Message----- >IF cell A1 contains the word "DAM" in any part of the cell then cell A4 >must equal "UNAVAILABLE" > > >the key he...

Excel should accomodate more number of rows than 65536, to load l.
It would be good to make excel sheet load more than 65536 rows. This is a common requirement for Data conversion/ Data Loading process. And it what databases handle very easily. -- HTH RP (remove nothere from the email address if mailing direct) "Laxman Charipally" <Laxman Charipally@discussions.microsoft.com> wrote in message news:8D26B56B-F22B-4E75-B491-1CDBFE2431F8@microsoft.com... > It would be good to make excel sheet load more than 65536 rows. > This is a common requirement for Data conversion/ Data Loading process. > ...

Count If Formula
Hi, I am looking for a formula that would calculate one cell if anothe cell has a letter. For Example: A B 1 X 7:00 2 Y 3:25 3 Q :25 4 x :45 I want excel to count in column A and find all of the "x's" and add u how much time "column b" it took. So I want excel to come up with 7:45 (adding b1 and b4 and any other "x's" that have a time in column b) Thanks -- 867503 ----------------------------------------------------------------------- 8675039's Profile: http://www.excelforum.com/member.php...