Excel 2007 : OFFSET formula

**Jabbajaw** · 04-20-2012, 06:35 PM

Hey all.. newbie here.
I have been reading through threads and haven't found one that applies exactly to what I'm looking for. Forgive me if it exists somewhere and I have not searched correctly.
I am trying to find a way to generate a formula that can be copied across every 3rd column, but will automatically give the result that is 2 cells below the last formula.

Offset problem.JPG

This is the formula I used to get the first value:
=OFFSET($L10,-8,-10,1,1)

However, I need to be able to copy this formula across (I have many cells to populate).

Let me know if more details are needed. Thanks for any help anyone can offer.

Jesse

**Haseeb Avarakkan** · 04-20-2012, 07:06 PM

In E11,

Please Login or Register  to view this content.

Then copy to other cells.

**Cutter** · 04-20-2012, 07:11 PM

One way would be (in E11 and copied over)
=INDEX($B:$B,2+COUNTA($D11:D11)*2)
assuming there will be nothing on that row except these formulas (as indicated by your sample)

actually, on second thought, this in E11 and copied over
=INDEX($B:$B,COLUMNS($B11:D11)/3*2)

**Jabbajaw** · 04-20-2012, 07:48 PM

Wow!!! You guys are awesome.
I've tried both solutions provided by Cutter and Haseeb and it looks like either will work. Once I try to take the formula and incorporate it into my REAL spreadsheet I'll see if one stands out. I really appreciate the quick response. I dream of being able to solve these types of problems on my own one day.
Thanks to both!!
Sincerely,
Jesse