+ Reply to Thread
Results 1 to 4 of 4

Click n' drag formula that auto-fills sequential sequence.

  1. #1
    Registered User
    Join Date
    06-25-2017
    Location
    Australia
    MS-Off Ver
    365
    Posts
    76

    Unhappy Click n' drag formula that auto-fills sequential sequence.

    For the life of me, I can't figure out how to search for this problem because it's so specific. I thought I could find something similar and tweak it to what I need, but so far no luck.

    I need to fill a sequence of numbers down a column next to another column of data, but the auto-fill sequence looks like this:

    Data B-1-1-1
    Data B-1-1-2
    Data B-1-1-3
    Data B-1-1-4
    Data B-1-1-5
    Data B-1-2-1
    Data B-1-2-2
    Data B-1-2-3
    Data B-1-2-4
    Data B-1-2-5
    ..etc

    Basically just put the formula into the first cell, then clicking and dragging it down as far as I need to. Is such a formula possible, or is this a bit too complicated for Excel?

    (Always starts with "B-1-", then goes "1-1" up to "1-5", then changes to "2-1" to "2-5", then "3-1 to "3-5"... and so on).
    Last edited by 2vbr; 02-17-2021 at 03:13 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Click n' drag formula that auto-fills sequential sequence.

    I just HAD to look at this one... loved the "sequential sequence" title!!

    assuming you want this to start in B2:

    ="B-1-"&1+INT((ROWS(B$2:B2)-1)/5)&"-"&1+MOD((ROWS(B$2:B2)-1),5)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    06-25-2017
    Location
    Australia
    MS-Off Ver
    365
    Posts
    76

    Re: Click n' drag formula that auto-fills sequential sequence.

    Quote Originally Posted by Glenn Kennedy View Post
    I just HAD to look at this one...
    That... worked absolutely perfectly.

    Wow. That was waaaaay faster than I ever thought possible. Thank you SO much!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Click n' drag formula that auto-fills sequential sequence.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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] Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names only
    By GonzCilla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2018, 02:16 PM
  2. Replies: 3
    Last Post: 01-30-2014, 05:31 AM
  3. [SOLVED] Clicking on cell that auto fills an X and removes all other auto fills in a range
    By ERaasio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-26-2013, 12:31 PM
  4. [SOLVED] Formula to re-sequence sequential numbers in a column
    By fearonc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-29-2013, 07:50 PM
  5. [SOLVED] Drag Formula's to continue a sequence
    By Sky188 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2012, 03:24 PM
  6. Replies: 2
    Last Post: 07-13-2012, 03:21 AM
  7. Drag formula sequence
    By Scrubber in forum Excel General
    Replies: 3
    Last Post: 12-17-2009, 02:02 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