+ Reply to Thread
Results 1 to 12 of 12

How to copy sequential vales into non sequential rows

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    How to copy sequential vales into non sequential rows

    I need help with the following.
    I have a set of values on rows in worksheet 1 that are sequential (i.e. B1, B2, B3….)
    I need to copy those values into a different worksheet in non-sequential order (6 columns apart with thins in between). Ideally what I need is a formula that helps expedite because there are 12 rows and 50 columns that I need to copy. Also, I need the value of sheet one that when it it changed it is also changed in sheet 2.
    To be exact what I need is the following.
    I have values in worksheet 1 in rows E9, E10, E11 etc.
    Thos values have to be imported into worksheet to as follows
    E9 goes to G6
    E10 goes to N6
    E11 goes to U6
    Etc.
    If someone can help would appreciate it.
    Thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to copy sequential vales into non sequential rows

    A few references in your prior post but using the example of Sheet1!E9:E11 to Sheet2!G6/N6/U6 respectively (ie every 7th column)

    Please Login or Register  to view this content.
    the above is based on assumption that H:M etc contain other formulae as implied in OP.

    note: change delimiters as nec. per your regional settings - you might need to use ; rather than ,

  3. #3
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to copy sequential vales into non sequential rows

    I got this formula to work =INDEX('[Sales Budgets 2011.xls]Presupuesto de Producion'!$E$9:$P$9,8+ROUNDUP(COLUMNS($G6:G6)/7,0)) but when I copy it to the other cells I get the same value. ei I only get the vaue of the first cell. Why?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to copy sequential vales into non sequential rows

    "other cells" is not very helpful - do you mean N6, U6 etc... ?

    If so, are you running in Auto Calculation Mode ?
    (i.e.: pressing F9 does not alter the values)

  5. #5
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to copy sequential vales into non sequential rows

    yes I mean N6, U6, AB6 etc. I tried F9 before and again right now but it did not work.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to copy sequential vales into non sequential rows

    Please copy the formula exactly as it appears in U6 and paste into your reply.

  7. #7
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to copy sequential vales into non sequential rows

    =INDEX('[Sales Budgets 2011.xls]Presupuesto de Producion'!$E$9:$P$9,8+ROUNDUP(COLUMNS($G6:G6)/7,0))

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to copy sequential vales into non sequential rows

    Can you outline how exactly you copied G6 to U6 ?

    If you copied the entry in G6 to U6 via Copy & Paste G6 the COLUMNS reference would have altered from $G6:G6 (G6) to $G6:U6 (U6) - in the above it has not.

  9. #9
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to copy sequential vales into non sequential rows

    I did use copy and paste. This is what appears in N6 =INDEX('[Sales Budgets 2011.xls]Presupuesto de Producion'!$E$9:$P$9,8+ROUNDUP(COLUMNS($G6:N6)/7,0))

  10. #10
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to copy sequential vales into non sequential rows

    but in N6 i get the same value as in G6

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to copy sequential vales into non sequential rows

    edit: hang on - just noticed you've changed from a vertical to horizontal vector.

    Please Login or Register  to view this content.
    your adjustment should be 4 rather than 8 (5th column is first record rather than 9th row per your original post)

  12. #12
    Registered User
    Join Date
    01-13-2011
    Location
    bogota, colombia
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How to copy sequential vales into non sequential rows

    I did you formula you sent but I game me the value of J9 so I adjusted de fornula to
    =INDEX('[Sales Budgets 2011.xls]Presupuesto de Producion'!$E$9:$P$9,0+ROUNDUP(COLUMNS($G6:N6)/7,0))
    and that did the trick. I am not sure why but it worked. THe first formule has to be original and the rest the above. Thank you once again. One day I would have to see why it works

+ 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