+ Reply to Thread
Results 1 to 2 of 2

Auto-Fill From Rows To Columns

  1. #1
    Registered User
    Join Date
    06-08-2010
    Location
    Cleckheaton, UK
    MS-Off Ver
    Excel 2007
    Posts
    1

    Auto-Fill From Rows To Columns

    Hi,

    I've got some data set out like so:

    Please Login or Register  to view this content.
    which I need in the following format:

    Please Login or Register  to view this content.
    The ID column is sorted. It increments by 1 every 5 rows, that's no problem. The QID, similar. The FQ2 column is generated by the next FQ1 minus 1, except for the one for QID 4 which is always 9999999. That's sorted too.

    The problem comes when I try to autofill the FQ1 and PRICE columns. It seems to jump to the row number that I'm auto-filling rather than increase the row number by one. It's quite tough to describe but easy to see in action if you wouldn't mind taking a look at the attached file.

    I've played about with absolute referencing and using INDIRECT and ADDRESS but am getting nowhere. I'm a web designer so don't have a great deal of contact with Excel but can usually get around any problems I've faced. I've tried and tried with this, can't find anything online to help and am bowing to the greater combined knowledge of this forum to give me a hand!

    Thanks in advance,

    Scott.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-16-2010
    Location
    UK
    MS-Off Ver
    2003 and 2007
    Posts
    252

    Re: Auto-Fill From Rows To Columns

    Based on your attached workbook:

    A2: =ROW(A14)

    B2: =IF(ROW(A1)=1,30,IF(MOD(ROW(A1)-1,5)<>0,B1,B1+1))

    C2: =OFFSET(INDIRECT("J"&MATCH(B2,$I:$I,0)),0,2*(COUNTIF($B$2:B2,B2)-1))

    D2: =IF(COUNTIF($B$2:B2,B2)=5,9999999,C3-1)

    E2: =COUNTIF($B$2:B2,B2)-1

    F2: =OFFSET(INDIRECT("K"&MATCH(B2,$I:$I,0)),0,2*(COUNTIF($B$2:B2,B2)-1))

    Select A2:F2 and drag down. See attached workbook.
    Attached Files Attached Files
    Last edited by pb71; 06-08-2010 at 08:32 PM. Reason: Added formulae for columns A and B

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1