Forum Discussion
Non-Consecutive Cell Referencing
- Dec 20, 2025
Thank you also for your help. I will stick to your proposal for the reasons you give. But I will play around with all suggestions just to get an idea of how and why these functions work. Have a Merry Xmas!
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.
Thank you also for your help. I will stick to your proposal for the reasons you give. But I will play around with all suggestions just to get an idea of how and why these functions work. Have a Merry Xmas!