Forum Discussion

MattKW1's avatar
MattKW1
Copper Contributor
Dec 18, 2025
Solved

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 origi...
excel
Formulas and Functions
office 365
  • MattKW1's avatar
    MattKW1
    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!

IlirU's avatar
IlirU
Brass Contributor
Dec 20, 2025

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

Resources