Autofill a formula with a series to start next row at the end of the previous series

[mbkr29]

I basically want my formula to skip every 3 rows. I want to be able to drag down these formulas, in particular the $C2:$C4 (first formula) and $C8:$C10 (second formula), respectively to look like $C14:$C16 for the third row and $C20:$C22 for the fourth row. Currently, when I drag down the first and second row, the third row autofill is $C4:$C6.

Below is my formula. (I am only focused on the portion mentioned above)

=INDEX('OPTION DATA '!$C2:$C4,MATCH(MIN(INDEX(ABS('OPTION DATA '!$C2:$C4-Sheet11!D2),0)),INDEX(ABS('OPTION DATA '!$C2:$C4-Sheet11!D2),0),0))

=INDEX('OPTION DATA '!$C8:$C10,MATCH(MIN(INDEX(ABS('OPTION DATA '!$C8:$C10-Sheet11!D3),0)),INDEX(ABS('OPTION DATA '!$C8:$C10-Sheet11!D3),0),0))

Thank you very much for any help in advance.

[:) Sixthsense :)]

I am not interested to change part of the formula could you please describe what you are trying to do in this part?

MATCH(MIN(INDEX(ABS('OPTION DATA '!$C2:$C4-Sheet11!D2),0)),INDEX(ABS('OPTION DATA '!$C2:$C4-Sheet11!D2),0),0)

[mbkr29]

I do not know what all the programming above means, but it is simply matching a cell "Sheet11!D2" to one of three options in cells "OPTION DATA $C2:$C4" For example cell "Sheet11!D2" is 65 and cell C2=62.5, C3=65, and C4=67.5 so it will match it to C3

*I found this formula by searching on this site a few days ago

[:) Sixthsense :)]

Please attach a sample workbook with expected output for providing the exact formula based on your data.

To Add a File - click advanced (next to quick post), scroll down until you see "manage attachments", click that and select "add files" (top right corner). click "select files", find your file, select file, click "upload", when the file shows up at the bottom left click 'done"(bottom right). click "submit reply"(remember the 1 MB limit, you may have to crop your file down to get it to a size that can be uploaded...)

[mbkr29]

I really appreciate your rapid reply, but I will not be able to post my workbook until tomorrow morning (about 10 hours) because it is saved on my flashdrive and work computer which are both in the office.

[:) Sixthsense :)]

If possible can you please just add some sample in a new workbook and show the expected output? If not then attach it after when you reach office. But expect a day delay from me since tomorrow I won't be having any access to system and net connection

[mbkr29]

I wish I could, but I can't. One of my computers has internet but no excel and the other has Microsoft products but my internet doesn't work. Ignoring my full equation and only looking at the top part of my initial posting is it poss to drag a series every three rows? I can test it tomorrow morning if so on my longer eqn and see if it works

[:) Sixthsense :)]

Sorry i don't like to suggest something without the knowing the expected outcome and the real usage of the functions

[mbkr29]

OK. Fair enough. I will post asap. Thank you very much for taking time to help

[mbkr29]

Please look at my sample workbook that I have attached. I am trying to scroll down on "Sheet11:W2" by referencing three cells in another tab. I want W4 to automatically scroll down to include "C14:C16" but it does not. Instead it autofills C4:C6

[mbkr29]

Problem Solved. Thank you

[:) Sixthsense :)]

Not able to open and see your attachment what is your data and all since NOW ACCESSING FROM MOBILE.

Glad you mentioned you solved it in your next post. Can you please attach the solved file also OR the formula in which you finally ended. Because it will be helpful for someone who comes here with this same type of query OR others will suggest you better than your current formula.

[mbkr29]

dilipandey replied to me earlier today:

Join Date:12-05-2011
Location:New Delhi, India
MS-Off Ver:1997 - 2013
Posts:5,418

Re: Autofill a formula with a series to start next row at the end of the previous series

Hi mbrk29,

See the attached file where I have used a defined name using below formula:-

=OFFSET('OPTION DATA '!$C$2:$C$4,6*ROW('OPTION DATA '!$A1)-6,0)

Marked the required ranges in yellow in option data sheet and then checked them using sum function in sheet11