Results 1 to 10 of 10

Thread: Using autofill on cells with formulas
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2012
    Location
    Texas
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using autofill on cells with formulas

    Is there a way. . .

    I am in Cell A1 and the formula is =E1. I want to copy my formula down but instead of the row # changing from E1, E2, E3, etc. I need the COLUMN to change from E1, F1, G1, H1, etc.

    Is this possible?

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    272
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using autofill on cells with formulas

    Hi
    is this what you mean
    Code:
    =INDIRECT("E"&ROW())
    Or

    Code:
    =INDIRECT(ADDRESS(ROW(),COLUMN()+4))
    Last edited by mohadin; Sep 11th, 2019 at 04:46 PM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using autofill on cells with formulas

    How about
    =INDIRECT("R1C"&ROW()+4,0)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Feb 2012
    Location
    Texas
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using autofill on cells with formulas

    Thank you! The formula works in A1 but when I drag it to A2 the E does not change to F. . .

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    272
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using autofill on cells with formulas

    Have you try
    =INDIRECT(ADDRESS(ROW(),COLUMN()+4))
    Last edited by mohadin; Sep 11th, 2019 at 04:53 PM.

  6. #6
    New Member
    Join Date
    Feb 2012
    Location
    Texas
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using autofill on cells with formulas

    It is still copying down the row but keeping the column the same. So, It is giving me E1, E2, E2 when I need E1, F1, G1, etc.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using autofill on cells with formulas

    Have you tried the formula in post#3?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Feb 2012
    Location
    Texas
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using autofill on cells with formulas

    I did try all of them (thank you all). After trying those got me thinking. . . INDEX($1:$1, 1, ROW(1:1))

    This Worked!!

  9. #9
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using autofill on cells with formulas

    Try this formula From Column(E) to Column(Z)
    Code:
    =INDIRECT(CHAR(69+ROWS($A$1:A1)-1)&1)
    Or This One Column(E) to Any where
    Code:
    =OFFSET($A$1,,ROWS($A$1:A1)+3)
    Last edited by salim hasan; Sep 11th, 2019 at 05:13 PM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using autofill on cells with formulas

    Quote Originally Posted by jmancha View Post
    I did try all of them (thank you all). After trying those got me thinking. . . INDEX($1:$1, 1, ROW(1:1))

    This Worked!!
    Glad you sorted it & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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