+ Reply to Thread
Results 1 to 7 of 7

Incrementing Colums while dragging Index formula across row

  1. #1
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Incrementing Colums while dragging Index formula across row

    Hello forum users and Gurus!!!

    I am trying to us the Index formula in Excel 2010 and I have a table with a large array of columns. Instead of going into each cell and modify the column reference in 149 cells, I was trying to see if there is a formula or modification to the Index formula so I can drag across the row and the column reference increments accordingly.

    This is the basic formula I am using...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If I drag this formula across the row, the column reference does not change.

    Ideas?

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,798

    Re: Incrementing Colums while dragging Index formula across row

    Perhaps =INDEX(A2:ES44,$A$46,column()) if you start in col 2. Otherwise add or remove a constant value to the column() function

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    Re: Incrementing Colums while dragging Index formula across row

    The $ signs anchor either the column (letter) or the row (number), so you will need to change those to suit what you want.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Incrementing Colums while dragging Index formula across row

    Try using COLUMNS($A:B) in place of the 2.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  5. #5
    Registered User
    Join Date
    10-19-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: Incrementing Colums while dragging Index formula across row

    Its sloppy, but create a row that counts the columns and then reference to that number in the index formula.

  6. #6
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Incrementing Colums while dragging Index formula across row

    Pepe-I modified the formula with your suggestion and got the result I was looking for. Thanks!!!

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,798

    Re: Incrementing Colums while dragging Index formula across row

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to change reference area automatically by dragging Index Match Formula down
    By Nuada in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 01:19 PM
  2. [SOLVED] Incrementing ROW when dragging across columns
    By jshaw82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2013, 04:16 PM
  3. Incrementing Index for paste function with each use of a macro
    By Iantaylora in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2012, 10:44 AM
  4. Dragging Across Columns, Incrementing Rows
    By Fpob in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-07-2012, 10:56 AM
  5. [SOLVED] Dragging formulas across columns while incrementing rows
    By gautamacharya in forum Excel General
    Replies: 4
    Last Post: 10-12-2011, 04:42 PM
  6. Dragging down and moving along colums, not rows
    By DerPumuckl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2010, 01:36 PM
  7. Incrementing hlookup row index
    By stevenson08 in forum Excel General
    Replies: 3
    Last Post: 11-11-2008, 08:17 AM
  8. Dragging & incrementing cell values
    By Terry Bennett in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2006, 07:20 PM

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