+ Reply to Thread
Results 1 to 7 of 7

Advancing a Formula every three lines

  1. #1
    Registered User
    Join Date
    06-28-2021
    Location
    Colorado, USA
    MS-Off Ver
    Office 365
    Posts
    25

    Advancing a Formula every three lines

    Hello, does anyone know how to advance a formula sequentially every three rows in excel? I just want the last cell reference to increase every three rows. When I fill down it skips sequential numbers.

    Here is an example of the formulas I am using in column A:

    Row1='[CCData 4.14.21 Master.xlsx]AA'!$AM1
    Row2='[CCData 4.14.21 Master.xlsx]AA'!$AO1
    Row3='[CCData 4.14.21 Master.xlsx]AA'!$AP1
    Row4='[CCData 4.14.21 Master.xlsx]AA!$AM2
    Row5='[CCData 4.14.21 Master.xlsx]AA'!$AO2
    Row6='[CCData 4.14.21 Master.xlsx]AA'!$AP2

    Thank you!

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Advancing a Formula every three lines

    A1=INDIRECT("'[CCData 4.14.21 Master.xlsx]AA'!$AM" & INT((ROW()-1)/3)+1)
    A2=INDIRECT("'[CCData 4.14.21 Master.xlsx]AA'!$AO" & INT((ROW()-1)/3)+1)
    A3=INDIRECT("'[CCData 4.14.21 Master.xlsx]AA'!$AP" & INT((ROW()-1)/3)+1)

    Highlight those 3 cells and drag down from the bottom right of A3

  3. #3
    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,893

    Re: Advancing a Formula every three lines

    Mmm. Not sure I'd do that... for two reasons. Firstly, the Master.xlsx file must be open, otherwise it'll retrun an error. Secondly, being volatile INDIRECT recalculates every time anything changes and can therefore dramrtically slow your sheet down.

    No..

    Assuming your formula starts in B1,B2 & B3 of some sheet in some file...

    =INDEX('[CCData 4.14.21 Master.xlsx]AA'!AM:AM,1+INT((ROWS(B$1:B1)-1)/3))
    =INDEX('[CCData 4.14.21 Master.xlsx]AA'!AO:AO,1+INT((ROWS(B$2:B2)-1)/3))
    =INDEX('[CCData 4.14.21 Master.xlsx]AA'!AP:AP,1+INT((ROWS(B$3:B3)-1)/3))

    And, as above, select all 3 and drag down.
    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

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Advancing a Formula every three lines

    In A2 then copied down

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Advancing a Formula every three lines

    A1: =INDEX('[CCData 4.14.21 Master.xlsx]AA'!AM:AO,ROUNDUP(ROWS($A$1:A1)/3,0),MOD(ROWS($A$1:A1),3)+IF(MOD(ROWS($A$1:A1),3)=0,3,0)), copy down

  6. #6
    Registered User
    Join Date
    06-28-2021
    Location
    Colorado, USA
    MS-Off Ver
    Office 365
    Posts
    25

    Re: Advancing a Formula every three lines

    Thank you all so much for your responses, you have saved me hours of time! Glenn Kennedy-I did end up using your formula.

    Thank you all!

  7. #7
    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,893

    Re: Advancing a Formula every three lines

    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. Copy list and add spaces between lines on a new tab and add formula on inserted lines
    By theglitch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2016, 11:35 AM
  2. Advancing formula
    By spankydata in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2006, 04:49 AM
  3. [SOLVED] Getting values in row and advancing
    By Billy B in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2006, 12:28 PM
  4. Advancing Down A List
    By Minitman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-05-2005, 10:50 PM
  5. [SOLVED] Advancing to the next cell
    By Conan Kelly in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2005, 02:05 AM
  6. [SOLVED] Copy Cell or Column without advancing formula reference?
    By sgluntz in forum Excel General
    Replies: 1
    Last Post: 03-17-2005, 04:06 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