Excel Magic Trick 1142: Get Every Other Row with Formula: INDEX and ROWS*2

How to do the opposite of this can anyone help me please i really need it. I want to go from order to every 5 rows

Good explanation!!

I appreciate the fact that you know EXACTLY what you're talking about. Brilliant tutorial. Thank you.

How to get result sum of 5 row set to drag bro

How to do exactly opposite, to insert all those every 2 or n rows with what I have in sep. column, Thanks. cool vid

what if you are trying to pull information from another spreadsheet page. what would the formula look???. as this formula is pulling from same spreadsheet page

Hello,

I have a 2 cells on sheet A independently link to another 2 cells on sheet Bbut I want when I drag the cells down in sheet A that a row is skip and the sequence continues and it skip again the follow sequence sheet A will copy A1 and A2 from SheetB into cells B1 and B2 of sheet A then skips B3 and on B4 records A3 and A4 from sheet B, how do I go about that ? Can what you show somewhat be applied but with changes ? If so how do I accomplish such a task that I can easily drag the formula down once created ?

Thank you so much!!! Been trying to figure it out for a while. I needed it to go across columns instead of rows and was able to do that with the information you provided.

Excellent! - just what I was looking for - thank you!

Hi how do make this work for odd rows 3,5,7 etc please?

I can get it to work for even rows but not odd ones

I’m trying to do it in a different sheet too can get it to work for even and not odd sooooo frustrating

This helped me solve my issue but was a slight different formula. For anyone looking to copy values from one column and return a new column but the values return everyother row I used the "row" function and multiplied the row by 1/2 which allowed me to insert a different set of data between each line item.

how to reverse it? I have two separate columns of data and I want it to arrange simultaneously every alternate row like the rough data you had at the begining.

Sorry I am late to the party, but you are a lifesaver. Thank you a million times

Super usefull, Thanks a lot brtohers

Will the same thing work if you needed to create a list of the names?

Thank you so much. This is what i was looking for. I was trying to resolve this for the past 2 days. you made my day now.. Thank you again...

thanks , please i have question on how to lock a row with the keyboard ??

Thank you very much, you've just saved me a lifetime of work!!!

Thank You! that helped me a lot! <3

Please make a tutorial on how to insert a new in between and copy all the formulas from the above cells to the cell below.

Life Saver

Bruh you the man for these videos.

Wow, that really helped me, thanks a lot! :) <3

Is there a way to do this where the output comes out on the same every other row? (leaving blank rows in between so you can still see who's sales data is in that new column you just made)

QQ: backstory: COLUMN A 1-600 , how can i add one day to the date FOR EVERY THREE ROWS?? MEANING EVERY 4TH ROW ADD ONE DAY TO THE COLUMN GOING DOWN

Hi, I am very new with Excel, like yesterday started working in it, and wow... so much power..
I have the next question if you don't mind helping me please... like you did, from one sheet I pulled the index to another, with a formula like you did, and the content are numbers, but now the problem is how to I sort this?? Because obviously you can not sort formulas.. and the main sheet from where I pull the index, has api addresses, refreshed every hour set up.

Great video. Taxing problem made simple.

how if its vice versa? The output will be the given input

Is there an advantage to using rows(C$2:C2) than just ROW(1:1) for an incrementing number? Also this shorter formula works as an alternative: =EVALUATE("A"&ROW(1:1)*2)

sir plz tell me how to inser nth row after row

Thanks a lot! You are a life rescuer!:)

Is there a way to do the opposite of this approach with a dataset. I have data in each row (lets say data in rows 1-10) and I need to move the data in a second sheet to every other row (1,3,5,etc.). Is there a way to do this?

Thank You, exactly that is what I wanted. In my data I needed to jump 7 rows.

i have so much data to organize this way. this is going to help so much

How can i get it to reference every 17th cell from a different worksheet? Your videos are very helpful, thank you.

Did not help me skip every 27 rows.

This is a remarkable video and very helpful, thank you!
However, I get stuck when I need to capture values that are on say every 10th or every 12th row (always a set amount of rows in between and on the same column). Any suggestion on how to tweak this formula to help with this problem?

I need to use this with an array that can grow up to 30k rows. Is there a performance advantage to this versus an address function?

I am trying to create a sheet which references another sheet but does the same trick. I want to bring the value from Sheet 1 X3 to Sheet 2 I2. How do I do this? After this is complete. I want to bring Sheet 1 X5 to Sheet 2 I3. Keep doing this while alternating the row from sheet 1 but copying over to every row in sheet 2

Can you demonstrate this but with transferring to other workbook or file?, not with the same sheet i'm having a hard time doing that. Ex. Transfer Data of a workbook to another workbook but with the use of this Index function. ty

What if i wanted to get the names instead of the numbers ? I tried the same way but no result.

***** , Yes use COLUMNS and lock the first Letter in the First Cell in the range, rather than the first Row in the Cell in the range.

ExcellsFun I have a project that im trying to put together i was wondering if you can help me tweak what i have already put together.  i trying to minimize human error due to the data input.  i would rather like to put the data in one time and have it calculate everthing vice enter data and copy formulas. any help would be great thanks.

Welcome Majk! 
Excel still amazes me. Your videos, in which you present solutions to the problems are also remarkable. I wish you every success.

Thanks for video !

Dear sir,

I have one query in excel which I am not able to form a formula.
My company deals in Cable TV business and a part of revenue comes from various TV Channels as placement amount which is in the form of a yearly contract.
I have to prepare monthly income to be book, which I do almost manually.
Is it possible to apply a formula that split the revenue in duration of the contract and revenue should be equal in all months.
For example:
If a contract is from 04/15/2014 to 04/14/2015 for Rs. 1,20,000. Then monthly revenue should be from
April’2014 Rs.5,000 and from May’14 to March’14 10,000 per month and for April’15 is 5,000.
Basically I want to keep revenue equal monthly.
If I am not able to illustrate the situation i can give you the excel sheet with data.
Thanks in advance.

Simple and effective.. thats mike!! :)

Hey mike... I have been following you for yrs now and have learned so much. Thanks you for what you do.
If I have a question with a how to approach an idea I have but not sure how I could structure it on excel, could I email you or contact you?
Thanks again.

Some classics :)
Thanks!