+ Reply to Thread
Results 1 to 18 of 18

Autofill formula down rows, but increment column reference

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    New York, new York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Autofill formula down rows, but increment column reference

    In Cell G20 I have: =(Sheet4!C8-Sheet4!C7)/Sheet4!C7

    In Cell G21 I want: =(Sheet4!D8-Sheet4!D7)/Sheet4!D7

    In Cell G22 I want: =(Sheet4!E8-Sheet4!E7)/Sheet4!E7

    How do I make it so I can just drag from cell G20 down to make it autofill how I describe above. Thanks
    Last edited by rchudgar; 04-25-2012 at 07:32 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    Should you be dividing by Sheet4!C7 in G20 (i.e. was that a typo)?

    Pete

  3. #3
    Registered User
    Join Date
    04-25-2012
    Location
    New York, new York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Autofill formula down rows, but increment column reference

    Quote Originally Posted by Pete_UK View Post
    Should you be dividing by Sheet4!C7 in G20 (i.e. was that a typo)?

    Pete
    Yes, thanks for catching that, I've edited the post.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    Okay, you could put this in G20:

    =(INDEX(Sheet4!8:8,ROW(A3))-INDEX(Sheet4!7:7,ROW(A3)))/INDEX(Sheet4!7:7,ROW(A3))

    and then copy it down as far as you need.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    04-25-2012
    Location
    New York, new York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Autofill formula down rows, but increment column reference

    Not sure about the A3 reference in your code, Pete. In A3 I have something completely unrelated to what is in G.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    You have to read it as ROW(A3), i.e. it returns 3, nothing to do with what A3 actually contains. As the formula is copied down, this part becomes ROW(A4), ROW(A5) etc. on successive rows, i.e. it returns 3, 4, 5 etc starting from G20 - this is the column number on row 8 and row 7 where you want to get your data from, i.e. from C8 and C7, then D8 and D7, then E8 and E7, and so on.

    I suppose I could have used ROWS(A$1:A3) instead and this might have been less confusing, but this involves more typing !! <bg>

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    04-25-2012
    Location
    New York, new York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Autofill formula down rows, but increment column reference

    Got it, it works! Thanks so much. Also, if I wanted to do the same thing with column H, how would I change the formula?

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    Do you mean that you want to copy it down column H (and if so, then which columns in Sheet4 would you be getting the data from), or that you want to get the data from column H in Sheet4 (in which case then what formula would you be using)?

    Pete

  9. #9
    Registered User
    Join Date
    04-25-2012
    Location
    New York, new York
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Autofill formula down rows, but increment column reference

    H18: =(Sheet4!A98-Sheet4!A8)/Sheet4!A8
    H19:=(Sheet4!B98-Sheet4!B8)/Sheet4!B8

    That's what I'd like to do.

  10. #10
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Autofill formula down rows, but increment column reference

    Just an idea..

    if you drag down, the rows change not the columns.

    you can change the column by dragging across -use a blank sheet for these.

    then copy and paste special, with transpose to put it in rows..........

    again i think this is not the best solution, someone may have done this. but the above is what i'm doing.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    Quote Originally Posted by rchudgar View Post
    H18: =(Sheet4!A98-Sheet4!A8)/Sheet4!A8
    H19:=(Sheet4!B98-Sheet4!B8)/Sheet4!B8

    That's what I'd like to do.
    Put this in H18:

    =(INDEX(Sheet4!98:98,ROW(A1))-INDEX(Sheet4!8:8,ROW(A1)))/INDEX(Sheet4!8:8,ROW(A1))

    then copy down.

    Please mark the thread as Solved if you now consider it to be so. Also, you can click on the "star" icon on any post that you think has helped you if you want to pass on thanks directly.

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    03-02-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Autofill formula down rows, but increment column reference

    I have a similar problem but cant seem to work out from above how I would do it.

    Here is what I have

    I have in cell D4 have =C4*Who!AR1
    I have in cell D5 have =C5*Who!AS1
    I have in cell D6 have =C6*Who!AT1


    and I want to increment C6, 7, 8, 9... on and Who!AU1, AV1, AW1, AX1......on.

    Please help.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    You should not try to hijack someone else's thread by asking your own question - see the Forum Rules. Instead, you should start your own thread and include a link back to this one if you think it is necessary.

    Pete

  14. #14
    Registered User
    Join Date
    03-02-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Autofill formula down rows, but increment column reference

    Sorry Pete,

    I shall repost.

  15. #15
    Registered User
    Join Date
    08-24-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Autofill formula down rows, but increment column reference

    Here is the simple way to make the columns incremental when we drag down the formula in rows to transpose into rows:

    A1 B1 C1 D1
    Names John Bill Brain
    Age 34 32 67

    Names Age
    John 34
    Bill 32
    Brain 67

    =INDIRECT(ADDRESS(1,ROW(A1)))

    =INDIRECT(ADDRESS(2,ROW(A1)))

    I am not sure if this is logical, but you can use this without using macros.
    Last edited by Pardha; 06-12-2018 at 05:29 AM.

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Autofill formula down rows, but increment column reference

    There is not much point in responding to a post from 5 years ago.

    Pete

  17. #17
    Registered User
    Join Date
    08-24-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Autofill formula down rows, but increment column reference

    I thought it may help if anyone searching for it. I don't know where to post, but just wanted to share.

    Thanks for pointing out.

    Pardha

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Autofill formula down rows, but increment column reference

    Thanks for the input, Pardha
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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