Newbie - Increment row numbers by 2 inside an if statement, cell reference is on another sheet

[andyandy]

Hi Everyone,

I'm a newbie and have searched for a solution to my problem for over an hour now and am a bit stumped. I'm hoping one of the pro's can help me.

I am trying to get the fill handle to increment my rows by 2 as I drag down. I have tried putting a second row and highlighting both to drag down to increment however this did not work.

Here is my formula that I am hoping to achieve this on.

=IF(Diary!F8-Diary!E8>0,(Diary!F8-Diary!E8),"")

The desired result would be for the next cell row to show =IF(Diary!F10-Diary!E10>0,(Diary!F10-Diary!E10),"") and so forth.

Hopefully I can one day become an expert in Excel.

Cheers
Andy

 

[Paradigm_Shift]

what year of excel are you using? The method of putting, for example, 2 in one cell, and 4 in the cell below it, then highlighting both and dragging down should trigger excel to start incrementing by 2.
I know this works in 2007 and 2010, but i'd be surprised if it doesn't do this in 2003, as well. If you ARE using 2003, and this method doesn't work, let me know. We'll work out a solution.

**UPDATE**
I'm sorry, I just realized what you are asking, and the method you attempted with the dragging would not work.

I DID however find this result on another forum:

If data starts in A1...you can try:

=INDEX($B$1:$J$1,2*(COLUMNS($A$1:A1)-1)+1) copied across

copied across where B1 is the first value to extract and A1:J1 is the entire data range.

Here's the link if above doesn't make sense: http://www.excelforum.com/excel-wor...ula-that-returns-every-nth-cell-in-a-row.html

 

[andyandy]

Hi, Thanks for getting back to me so quickly.

I have tried using this method with numbers, i.e. 2 in cell A1 and 4 in cell A2 and dragged the fill handle down. This works perfectly.

However, when I try with my formulas that reference cells in another worksheet i.e. =IF(Diary!F8-Diary!E8>0,(Diary!F8-Diary!E8),"") the increments only increase by 1 no matter what I do.

I am using excel 2010. Basically I just need the numbers next to the Column references to increase by 2 each time.

Cheers
Andy

 

[Paradigm_Shift]

Andy,

I misread your original post, and edited my reply (4 minutes after you replied, but I hadn't gotten your reply yet...oops). I'm sorry for the misunderstanding.

Anyway, check out the link in my UPDATE. Try that out and if that still doesn't work, let me know what's still going wrong.

 

[andyandy]

Hi Paradigm Shift, Thanks for the link. I am trying to apply it to my formula and am having a bit of difficulty. I was hoping for suggestions that work on my formula. I'll keep trying to apply it and see what I come up with, but thank you for your responses.

Cheers
Andy

 

[andyandy]

Hi Paradigm Shift,

Thank you very much for that information. After an hour or so I applied it to my formula shown below.

=INDEX(Diary!$F$8:$F$71,2*(ROWS(Diary!$F8:$F$8)-1)+1)-INDEX(Diary!$E$8:$E$71,2*(ROWS(Diary!E8:$E$8)-1)+1)

I had to skip rows so I changed the Columns to rows and set my ranges.

Now I am very happy.

Cheers
Andy