Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Autofill across row from a column (Excel 2000>)

    Hi,
    Is it possible to use autofill to fill a row that collects its values from a column in a table. What would the formula look like to do this?

    Hope this is clear.

    PS: I was asked this by a collegue. I know that transpose can work but they would like to use autofill for some reason.
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofill across row from a column (Excel 2000>)

    1) It is possible to create custom lists in Excel (Tools | Options..., Custom Lists tab). Say, for example, that you create a custom list

    EASTERN CAPE
    FREE STATE
    GAUTENG
    KWAZULU-NATAL
    MPUMALANGA
    NORTHERN CAPE
    LIMPOPO
    NORTH WEST PROVINCE
    WESTERN CAPE

    If you type Eastern Cape in a cell, then drag the fill handle of the cell to the right, Excel will enter Free State, Gauteng etc. in the neighboring cells.

    2) Say that you enter the above list in A1:A9.

    You want to refer to the list in a row, starting in D2. Enter the following formula in D2:
    <code>
    =INDEX($A$1:$A$9,COLUMN()-3)
    </code>
    The -3 is because D2 is in the 4th column, and we want to refer to the 1st column. The formula can be autofilled to the right.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofill across row from a column (Excel 2000>)

    Perfecto....the formula is easier than I expected! I was assuming you needed to use Offset and Indirect and a host of others in some nested function!

    I am quite surprised.

    Tx a million!!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •