Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,577
    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,706
    Thanks
    60
    Thanked 89 Times in 82 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
    3,034
    Thanks
    157
    Thanked 798 Times in 728 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,577
    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,436
    Thanks
    3
    Thanked 259 Times in 237 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
  •