Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts

    Increment column Ref by 2 when copying formula from Left to Right

    I have the following formula in B3

    Code:
    =INDEX(Salesbr!M:M,MATCH($A$3,salesBR!$B:$B,0))
    I need M:M to increment by two columns when I copy it into C3 i.e. =INDEX(Salesbr!O:O,MATCH($A$3,salesBR!$B:$B,0))

    I then need O:O to increment by 2 columns when I copy into D3 etc


    Code:
     =INDEX(Salesbr!Q:Q,MATCH($A$3,salesBR!$B:$B,0))

    It would be appreciated if someone could assist me

  2. #2
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,653
    Thanks
    53
    Thanked 83 Times in 77 Posts
    Is B3 an ARRAY FORMULA, Howard?

    I don't particularly like this array formula, but it works:

    Code:
    =INDEX(INDIRECT("Salesbr!"&CHOOSE(COLUMN()-1,"M","O","Q","S","U")&":"&CHOOSE(COLUMN()-1,"M","O","Q","S","U")),MATCH($A$3,Salesbr!$B:$B,0))
    CTRL+Shift+Enter
    Last edited by kweaver; 2018-04-16 at 19:57.

  3. The Following User Says Thank You to kweaver For This Useful Post:

    HowardC (2018-04-16)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,986
    Thanks
    156
    Thanked 779 Times in 709 Posts
    Howard,

    Only if KW's approach does not work, you can try this Code by giving it a short cut CTRL-Z

    Code:
    Public Sub CopyFormula()
    x = ActiveCell.Formula
    s = Asc(Split(Split(Split(x, ",")(0), "!")(1), ":")(0)) + 2
    ActiveCell.Offset(0, 1).Formula = "=INDEX(salesBR!" & Chr(s) & ":" & Chr(s) & ",MATCH($A$3,salesBR!$B:$B,0))"
    End Sub
    Select B3 with the formula then click CTRL-Z and the formula will be copied to C3 (one cell to the right) with the advancing Columns by 2 and so on .

    HTH
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2018-04-16)

  6. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,544
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks for the help. much appreciated

  7. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,367
    Thanks
    3
    Thanked 229 Times in 209 Posts
    As a regular formula, you could add a column to your INDEX function and widen the original range:

    =INDEX(SalesBr!$M:$IV,MATCH($A$3,SalesBr!$B:$B,0), COLUMNS($A$1:A$1)*2-1)
    Regards,
    Rory

Posting Permissions

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