+ Reply to Thread
Results 1 to 8 of 8

How to increment an absolute cell reference

  1. #1
    Registered User
    Join Date
    01-05-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to increment an absolute cell reference

    I am trying to increment an absolute cell reference in an Excel spreadsheet column.

    What I want to be able to do is increment the absolute cell reference by 7 reslutling in increasing each row number by 7 starting with

    ='[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$5

    resulting in the following concecutive rows

    ='[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$12
    ='[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$19
    ='[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$26

    etc.

    I have multiple rows and columns. Any ideas would be appreciated.
    Last edited by acharletta; 01-05-2013 at 06:04 PM.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: How to increment an absolute cell reference

    You can use your original formula in the first row:

    ='[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$5

    Then for the second row, you can try this, then fill down:

    =INDIRECT("'[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$"&5+(ROWS($A$1:A1)*7))

    Let me know if it works for you.

    I should note that to use the indirect function across multiple workbooks, the workbook being referenced must be open. So it is not the best solution.

    - Moo
    Last edited by Moo the Dog; 01-05-2013 at 06:28 PM.

  3. #3
    Registered User
    Join Date
    01-05-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to increment an absolute cell reference

    Fantastic!!

    the only problem is that when I copy the formula to another column I have to change the Column reference letter. Any other brillant ideas and I do mean that as a compliment. I have spent hours racking my brains, looking through heip, etc. but could not figure out the syntax that I needed for the Indirect command. Obviously, I am one hell of a lousy Excel user ;-)

    Thank you so much.

  4. #4
    Registered User
    Join Date
    01-05-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to increment an absolute cell reference

    Understand, however, I have no other solution and I do have the other spreadsheet open when I am updating but I do appreciate your input on that too.

  5. #5
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: How to increment an absolute cell reference

    Not sure if your columns will be sequential (hopefully!) as in column J, then K, then L...

    This formula should return the values in column J for the first column you enter it into, then column K for the next column, etc.:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    - Moo

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to increment an absolute cell reference

    One way...

    Let's assume you want the first formula entered in cell A2 and then copied down...

    =INDEX('[XOM January 2013 Triage Team Metrics.xlsx]Africa Jan 2013'!$J$5:$J$100,ROWS(A$2:A2)*7-7+1)

    Adjust for the correct end of range.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    01-05-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to increment an absolute cell reference

    Moo, the last formula is not working as you anticipated. It is not returning any values. I want to say thanks again for all your help. Made my life a lot easier.

  8. #8
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: How to increment an absolute cell reference

    I'm glad to help with what I can... that second formula seemed to work for me, but my dummy spreadsheet probably isn't set up the same way as your data is laid out...

    Perhaps Tony could offer more advanced advice going forward to help you out - he knows his stuff.

    - Moo

  9. #9
    Registered User
    Join Date
    01-05-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to increment an absolute cell reference

    Than would be great. I need all the help I can get. By the way, my name is also Tony.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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