Using Autofill with Offset()

[Kentcomp]

I need to create a series of text entries in Column B (rows 3, 4, 5, ...) pulling from a series of tables that are stored in column J on every 7th row.

For example my data is:

J3 Foo
J10 Bar
J17 Hello
J24 World

...and I'd like the entries to appear in B3 as Foo, B4 as Bar, B5 as Hello, B6 as World, etc.

I've tried a number of the formulas that I have found on the Internet and particularly in MrExcel but they either haven't worked or are overly complicated (and still haven't worked). I'd like to Autofill the formula down from B3 to B4 to B5, etc. Can someone please tell me what formula I need to do this? Your help is greatly appreciated.

Details:

• I am running Excel 2010 on Windows
• I have to share the spreadsheet with a Mac user so I'd just like a formula that doesn't require VBA.

 

[Jonmo1]

Welcome to the baord...

Try

=INDEX(J:J,((ROWS(A$1:A1)-1)*7)+3)

DO NOT CHANGE A$1:A1 - this has nothing to do with the location of your data.
7 = Inteveral (every 7 rows)
3 = Row to begin in (J3)


Hope that helps.

 

[Kentcomp]

That did it Jon. Thank you so much!!!

 

[violet_universe]

that did it jon. Thank you so much!!!

=offset($j$3,(row(1:1)-1)*7,)
or
=INDEX(J:J,((ROWS($1:1)-1)*7)+3)