Non-Consecutive Cell Referencing

May I be a bit harsh.  In my opinion you are beginning your adventure of learning Excel by focussing upon obsolete techniques, maybe forced upon you by the use of legacy software.  In these days of dynamic arrays, the concept of relative referencing an dragging formulas down is over-complicated and confusing!

If you wish to reference every 5th element of an 'array', one way of doing it is to wrap the array across 5 cells at a time.

wrapped
= WRAPROWS(array, 5)

You will see the references you require in the first column.  To select the column one simply chooses it

every5th
= CHOOSECOLS(wrapped, 1)

These are most useful in situations in which the other columns are used in separate calculations.  If they are not used, INDEX will work

= INDEX(array, SEQUENCE(n,,, 5))

Putting these together, either by nesting or by using the LET function to define variables

every5th
=  CHOOSECOLS(WRAPROWS(array, 5),1)

or (a bit more advanced)

every5th
= LET(
    wrapped, WRAPROWS(array, 5),
    CHOOSECOLS(wrapped, 1)
  )

every5th
= LET(
    k, SEQUENCE(n,,,5)),
    INDEX(array, k)
  )

I am not trying to get at you.  It is simple that I believe that you should be encouraged to start your journey by travelling forwards, not back to the past.

Maybe the other expert contributors to this discussion have a different opinion.

You can do this with a simple formula, no INDIRECT needed.

In B3, use:

='Orig'!A3

In B8, use:

='Orig'!A4

The trick is to automate that pattern. Put this formula in B3 and copy it down:

='Orig'!A3+INT((ROW()-ROW($B$3))/5)

This makes the source cell move down by 1 in Orig every 5 rows in column B.

Simple, clean, and beginner-friendly. 

Nobody has yet asked a question that I wanted to ask from the start: Why?

You don't have to answer, of course; may even view it as an impertinent question: what business is it of mine to question your question?!

But I am curious. It's an unusual request, and seems like a strange task. In general I'm a person who likes to keep rows and columns filled, not have blank rows or columns between sets of data, yet here you're going to lengths to create blank rows! So you've piqued my curiosity.

Are you willing to share the purpose? How you'll be filling or using those newly created blank rows?

MattKW1​,

I think you can use the following formula which is a dynamic formula and does not use the INDIRECT function which is a volatile function.

=IFERROR(TOCOL(EXPAND(Orig!A3:A10,, 5)), "")

or this formula:

=SCAN("", IFERROR(TOCOL(EXPAND(Orig!A3:A10,, 5)), ""), LAMBDA(a,b, IF(b = "", a, b)))

The first formula leaves 4 empty cells after each cell with data, while the second formula fills the empty cells.

Change the range in the formula according to your need.

Hope this helps.

IlirU

MattKW1​ 
Option from m_tarler​ is much better - from an efficiency perspective as well. If you want to stick to INDIRECT the following addresses his point re. locked column A on the Orig sheet, the sheet name remains locked though *:

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

* A workaround exists with Excel >/= 2013

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.

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.)

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