Auto fill / Transpose

Posted on 2012-08-14
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
    LVL 19

    Assisted Solution

    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

    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

    Prefer to avoid macros when possible. Thanks very much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now