Closed Thread
Results 1 to 19 of 19

Drag horizontally and copy vertical cells

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    5

    Drag horizontally and copy vertical cells

    Hello
    i am newbie to excel and i need some help in this issue
    i have copied A1 cell from sheet 1 into A1 cell from sheet 2
    what i need to do is to drag A1 of sheet 2 horizontally (B1, C1, D1) and get the vertical values (rows) of sheet 1: A2, A3, A4, etc...

    Is it possible
    because i would to deal with sheet 1 only and get the results on sheet 2 automatically

    Thank you in advance

  2. #2
    Forum Contributor
    Join Date
    10-27-2013
    Location
    Columbus Ohio
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Drag horizontally and copy vertical cells

    Dp you need the SUM, or just the values?

  3. #3
    Forum Contributor
    Join Date
    10-27-2013
    Location
    Columbus Ohio
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Drag horizontally and copy vertical cells

    If you just need value then paste this is A1 of sheet 2 and drag it acrossed. It will grab any values in the corresponding cell on sheet 1
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-05-2013
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Drag horizontally and copy vertical cells

    Just the values

  5. #5
    Forum Contributor
    Join Date
    10-27-2013
    Location
    Columbus Ohio
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Drag horizontally and copy vertical cells

    Okay the formula I posted will copy the values of A1 in sheet 1 to A1 in sheet 2. Dragging it to B1 does B1 sheet1 to B1 sheet2 etc.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Drag horizontally and copy vertical cells

    You can do this with INDEX and COLUMNS
    IF your data is in sheet1 A1:A1000 and your formula is in A1 of Sheet2

    =INDEX(Sheet1!$A$1:$A$1000, COLUMNS($A$1:A1))
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Contributor
    Join Date
    10-27-2013
    Location
    Columbus Ohio
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Drag horizontally and copy vertical cells

    I guess it helps if I pay attention to the fact he wanted Vertical values. Disregard shamskh2. That just gives horizontal values.

  8. #8
    Registered User
    Join Date
    12-05-2013
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Drag horizontally and copy vertical cells

    I am out of office now...will try it and tell u how it worked
    Thanks

  9. #9
    Registered User
    Join Date
    12-05-2013
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    5

    Cool Re: Drag horizontally and copy vertical cells

    @chemist B
    it worked very well.
    Thank you very much

  10. #10
    Registered User
    Join Date
    12-05-2013
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Drag horizontally and copy vertical cells

    Do you know if i can use the same formula but to skip one cell constantly
    i mean when the row are copied i want to see in sheet 2
    A1, A3, A5...etc and not A1, A2, A3, A4, A5
    without leaving blank cell or a 0 instead.

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Drag horizontally and copy vertical cells

    May be this.....

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  12. #12
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Drag horizontally and copy vertical cells

    =INDEX(Sheet1!$A$1:$A$1000, COLUMNS($A$1:A1)*2-1) for 1,3,5,...

    =INDEX(Sheet1!$A$1:$A$1000, COLUMNS($A$1:A1)*2) for 2,4,6,8.......
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  13. #13
    Registered User
    Join Date
    12-05-2013
    Location
    Cairo
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Drag horizontally and copy vertical cells

    Thank you a lot
    both worked very well

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Drag horizontally and copy vertical cells

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Registered User
    Join Date
    04-10-2020
    Location
    Wilmington, Delaware
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Drag horizontally and copy vertical cells

    ChemistB,
    I have tried this and it does not work for me.
    My data is in sheet1 Cell S77.
    I typed the following formula into Cell F4 of Sheet 2

    INDEX('sheet1'!$F$4:$S$77, COLUMNS($F$4:F4))

    Thanks

  16. #16
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,873

    Re: Drag horizontally and copy vertical cells

    Jmpic44 please check the forum rules, particularly #4 about posting a question in another thread.
    Please start your own post, they are free
    You can copy the url for this post and include it if you think this post is relevant.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  17. #17
    Registered User
    Join Date
    04-10-2020
    Location
    Wilmington, Delaware
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Drag horizontally and copy vertical cells

    Thank you Sambo kid for your help.
    I just registered yesterday afternoon and am not well versed on the how to's.
    I was just looking for assistance.

  18. #18
    Registered User
    Join Date
    04-10-2020
    Location
    Wilmington, Delaware
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Drag horizontally and copy vertical cells

    How do I start a post.
    I have searched your site and I believe I have not been afforded this ability.

  19. #19
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,873

    Re: Drag horizontally and copy vertical cells

    At the top is a blue post a thread button in each forum, just click that.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. formula to drag horizontally to equal vertical cells
    By jasonanthony in forum Excel General
    Replies: 6
    Last Post: 05-14-2015, 03:24 AM
  2. Replies: 5
    Last Post: 11-28-2012, 10:51 AM
  3. Replies: 1
    Last Post: 01-16-2012, 11:31 PM
  4. Replies: 2
    Last Post: 03-29-2006, 11:45 AM
  5. [SOLVED] [SOLVED] how to align vertical cells horizontally
    By Trice in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-12-2005, 01:05 PM

Tags for this Thread

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