Forum Discussion

MattKW1's avatar
MattKW1
Occasional Reader
Dec 18, 2025

Non-Consecutive Cell Referencing

Hi, folks.

I'm attempting to create a spreadsheet that contains links from consecutive cells to consecutive cells in another worksheet that are separated by 5 intervening cells.

I'll call the original consecutive spreadsheet "Orig" (for original).
So, I know that if I put "='Orig'!A3" in cell B3 and then copy that down, it will update the relative formula consecutively, i.e. B3='Orig'!A3, B4='Orig'!A4, B5='Orig'!A5, B6='Orig'!A6.... that much I get.

What I need to do is find a way to do the same thing, but to increase the resulting link.....so that if I copied the formula down column B I would get: B3='Orig'!A3, B8=Orig'!A4, B13='Orig'!A6', etc so that the new worksheet is moving down 5 cells relative to the Orig sheet consecutive order.

I've read where someone used a formula using the INDIRECT function but that's beyond my beginner level.

Many thanks, and merry Xmas to all! 

excel
Formulas and Functions
office 365

3 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Dec 19, 2025

    You do NOT need to use INDIRECT for this and recommend against using INDIRECT.

    If you are OK with or prefer copy down (as opposed to an array formula that will spill (especially if you plan to 'fill' in the rows inbetween, then:

    a) B3 = INDEX('Orig'!A:A,(ROW()-ROW($B$3))/5+ROW('Orig'!$A$3))

    b) highlight cells B3:B7 then either drag the fill down dot (the dot in lower right corner of highlighted section) or copy and then highlight area to paste in and paste

     

    so why don't use INDIRECT and use the INDEX above instead and why use ROW($B$3) instead of just typing 3?  Basically it makes it more robust in case you insert or delete lines or move things around excel will shift those cell references accordingly.  For example you decide to add a header to the sheet and insert 3 rows above row 1 then the formula would automatically shift to be in cell B6 and those references would also reference $B$6

    EDIT: BTW, Lorenzo​ also did similar in their second option.  The only difference in this case is using INDIRECT 'locks' in the sheet name and column as "Orig!A" but using INDEX above (or there are other options like OFFSET you could use instead) then if you change the sheetname or insert a column to the left of the existing column A it will shift/change name accordingly.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Dec 19, 2025

    MattKW1​ wrote: I've read where someone used a formula using the INDIRECT function but that's beyond my beginner level.

    To which I respond, don't sell yourself short. The only way any of us learn without taking courses (and maybe even then)--expecially in Excel--is by playing around with those things we don't yet know. "Playing around" sometimes goes by the term "trial and error." INDIRECT definitely is one of those functions, one of many, where it often takes a bit of trial and error to get it right, but it's truly far better to go through that than to just have the answer handed to you.

    May I recommend for future times when you are feeling like you're in over your head that you acquaint yourself with ExcelJet, where there are really helpful descriptions, along with examples, of functions like INDIRECT. I've learned a lot there myself, over recent years, by adding what's shown there with a little bit of playing around myself.

    You're not going to break anything by trial and error (I do recommend that you save a backup copy of your workbook first, especially if it's at, you know, work.)

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 19, 2025

    Hi

    On your other sheet in B3:

    =INDIRECT( "Orig!A" & QUOTIENT( ROW(B3), 5 ) +3 )

    EDIT: OR - safer:

    =INDIRECT( "Orig!A" & QUOTIENT( ROW(B3)-ROW(B$3) +1, 5 ) +ROW(Orig!A$3) )

    Merry Christmas 2 

Resources