How to create and copy a formula which takes data from every second column and adds it to another sheet in a row

Question

I have a sheet ("Names") with data starting in B1 and continuing across the row with a named value in every second cell. (eg B1, D1, F1 etc). I would like to use a formula to insert this in a second sheet ("List") as a series of rows. (Eg. B6, B7, B8 etc) I have tried to add an OFFSET formula to the "List" sheet but can't copy this down the page. I have tried:

    =OFFSET('Names'!B$1,0,2)

However when I copy this formula down the column it continues to reference the same cell. How do I get this to increment so I end up with a formula in each row of the "List" such as:

    =OFFSET('Names'!B$1,0,2)
    =OFFSET('Names'!B$1,0,4)

Which would return a list of names from the first sheet? For example:

• B1
• D1
• F1
• etc

I would like it so if more rows are needed in the "List" to correlate to new columns in "Names", the formula can be copied down the row.

Any help is greatly appreciated

Answer 1

Since, i have shared the solution in comments, hence sharing it in answers as well, so that some one looking for a solution may find it useful in future,

• Formula used in cell B6

=OFFSET(Names!$B$1,0,(ROW(A6)*2)-12)