Forum Discussion
Non-Consecutive Cell Referencing
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.