Search code examples
excelnonsequential

Easy One - reference to non-sequential cells

I'm sure this has been asked before, but I couldn't figure out the right search terms to get the answer I was seeking, so apologies...

I have one worksheet with regular but non-sequential data (i.e. values in A1, A11, A21...)

In another sheet, I want to be able to put in a formula to get the values from those cells, and have it be easily scalable so I can drag the formula down and continue to get every nth cell from Sheet1. It looks like OFFSET will work, but it's not that elegant. VBA seems like overkill...Is there a better way?

Thanks!

Solution

  • Either Offset like you said, or something like this would be on the other sheet in Row 1 and copied down (assuming the first sheet is called 'sheet1', or change to the actual name:

    =INDIRECT(ADDRESS(ROW()*10-9,1,,,"sheet1"))

    Offset would be even clearer:

    =OFFSET(Sheet1!A1,((ROW())-1)*10-ROW()+1,0)