Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Auto fill / Transpose

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-08-23
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
Question by:cnrlzen
  • 2
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 2000 total points
ID: 38292471
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"
LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 38292475
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


Author Closing Comment

ID: 38325274
Prefer to avoid macros when possible. Thanks very much.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question