Auto fill / Transpose

When using the auto fill feature in exel, is there a way to drag the cursor vertically and have it fill the cells based on a horizontal sequence?
I have tried to drag horizontaly and then transpose but this does not work as i need the direct cell referencing and doing so causes and error to these references.

I want to enter =B2 into a cell then autofill downwards to arrive at the list...
Instead i drag and end up with
Who is Participating?
Arno KosterCommented:
you can use the offset line inside of a macro, but you could also manually enter the formula in cell D8:

=OFFSET($B$2, 0, ROW(D8)-8)

Open in new window

Arno KosterCommented:
there are multiple options of tackling it, deciding which one is the most appropriate is up to you.

use macro code to generate the formulae For pos = 1 To 5 Range("B" & pos).Formula = "=" & Cells(2, pos).Address Next pos

use "OFFSET" in the formula itself Range("D8").Formula = "=OFFSET($B$2, 0, ROW(D8)-8)"
the last option could actually be extended downwards by autofilling and retain the expected functionality.
The code does need to be adapted to the actual place where is it first put: cell D8 leads to "row(D8)-8", whereas putting it in cell D7 would lead to "row(D7)-7"
cnrlzenAuthor Commented:
Prefer to avoid macros when possible. Thanks very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.