+ Reply to Thread
Results 1 to 7 of 7

Incrementing column letters and row numbers at different intervals

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    12

    Incrementing column letters and row numbers at different intervals

    Hi all, I've got an Excel workbook with two sheets...one named "Sheet1" and one named "formulas".

    In Sheet1, cells A20 through A25 look like this:

    =formulas!A2
    =formulas!B2
    =formulas!C2
    =formulas!D2
    =formulas!E2
    =formulas!F2

    I can't seem to find a way to tell Excel that I want to autofill cell A26 on down to look like this:

    =formulas!A3
    =formulas!B3
    =formulas!C3
    =formulas!D3
    =formulas!E3
    =formulas!F3
    =formulas!A4
    =formulas!B4
    =formulas!C4
    =formulas!D4
    =formulas!E4
    =formulas!F4
    =formulas!A5
    =formulas!B5
    =formulas!C5
    =formulas!D5
    =formulas!E5
    =formulas!F5

    and so on.

    Do any of you fine folks know how I could accomplish this? Thanks!
    Last edited by CrazyChipmunk; 03-03-2017 at 02:20 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Incrementing column letters and row numbers at different intervals

    Paste formula into cell A20 and fill down until you get bored.


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Incrementing column letters and row numbers at different intervals

    You can put this formula in cell A20 of Sheet1:

    =INDEX(formulas!$A:$F,INT((ROWS($1:1)-1)/6)+2,MOD(ROWS($1:1)-1,6)+1)

    then copy it down as far as you need to.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    06-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Incrementing column letters and row numbers at different intervals

    mehmetcik and Pete_UK, first let me start off by saying you guys are awesome.

    I'm sorry I forgot to mention a vital piece of information. On the "formulas" sheet, column E is intentionally left blank. If I use the formulas you provided, every time column E is referenced (A24, A30, A36, A42...) a 0 is shown. How would I edit the formulas you provided to leave A24, A30, A36, A42 and so on blank instead of showing a 0?

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Incrementing column letters and row numbers at different intervals

    You could change my formula to this:

    =IF(MOD(ROWS($1:1)-1,6)+1=5,"",INDEX(formulas!$A:$F,INT((ROWS($1:1)-1)/6)+2,MOD(ROWS($1:1)-1,6)+1))

    then copy down.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    06-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Incrementing column letters and row numbers at different intervals

    That did the trick. Very nice! Thanks for the help Pete_UK and mehmetcik!

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Incrementing column letters and row numbers at different intervals

    You're welcome - thanks for the rep.

    Pete

+ 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. Copy data and paste to another column incrementing that column to next empty cell
    By edens2021 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2015, 11:43 AM
  2. Replies: 1
    Last Post: 08-27-2014, 11:05 AM
  3. Replies: 2
    Last Post: 03-14-2014, 05:00 PM
  4. [SOLVED] Reading the last 2 letters in a combobox value and enter those 2 letters into column
    By emilyloz in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-26-2013, 09:26 AM
  5. [SOLVED] Syntax to add incrementing column letters to a string
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2012, 09:50 PM
  6. Incrementing a string of letters in a cell
    By pimpmyworksheet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2010, 12:05 PM
  7. [SOLVED] Incrementing letters
    By Opinicus in forum Excel General
    Replies: 3
    Last Post: 11-03-2005, 10:10 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