Results 1 to 7 of 7

Thread: increment cell reference

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Location
    Tamworth. UK
    Posts
    607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default increment cell reference

    in a cell H3 i have ref to =C3

    i need H4 to ref cell =B3

    i need H5 to ref cell =C4

    and repeat down many rows . if i copy down excel doesn't work out i need the pattern to repeat. how can i simply copy down?

    i.e then in H6 need =B4
    H7 =C5
    H8 =B5
    and so on

    TIA
    Last edited by leecavturbo; Jul 19th, 2019 at 07:49 AM.
    Happy Excelling , always learning

  2. #2
    Board Regular
    Join Date
    Sep 2015
    Location
    the Netherlands
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: increment cell reference

    try INDIRECT in combination with ROW
    hit F1 for more info

  3. #3
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: increment cell reference

    try this in H3, and copy down

    Code:
    =OFFSET($A$1,ROUND((ROW(A3)+2)/2,0)-1,IF(MOD(ROW(A3),2)=0,1,2))

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Location
    Tamworth. UK
    Posts
    607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: increment cell reference

    Quote Originally Posted by AlanY View Post
    try this in H3, and copy down

    Code:
    =OFFSET($A$1,ROUND((ROW(A3)+2)/2,0)-1,IF(MOD(ROW(A3),2)=0,1,2))
    worked perfectly much thanks
    Happy Excelling , always learning

  5. #5
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: increment cell reference

    you're welcome

    the formula can be simplified as

    Code:
    =OFFSET($A$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))
    with same results
    Last edited by AlanY; Jul 19th, 2019 at 08:49 AM.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Location
    Tamworth. UK
    Posts
    607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: increment cell reference

    care to roughly explain how it works?
    i may be able to adapt it in future
    Happy Excelling , always learning

  7. #7
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: increment cell reference

    Quote Originally Posted by leecavturbo View Post
    care to roughly explain how it works?
    i may be able to adapt it in future
    Code:
    =OFFSET($A$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))
    the offset() function is refer to a range (a cell in your requirement) from a reference cell ($A$1) by offsetting it with rows and columns.

    e.g. offset(A1,1,1) will offset A1 by 1 row and 1 column that gives B2 etc
    from then on is just to find the patterns so that it can copy down from H3

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
  •