Google Sheets, Trying to reference a cell in another sheet using a formula to determine the row numb-CodePudding

I am trying to reference cells from another sheet in the same workbook in Google Sheets, but I don't want to have to select the exact cell for every cell reference. What I need to do is reference a cell in another workbook for which the row number is 4 times the row number in which the formula appears. For example, in Sheet 1, cell A1, I could use =Sheet2!$A$4, but I don't want to have to do that all the way down my sheet. What I want to do is write a formula that tells the formula "go to Sheet2 and give me the value of the cell in column A that has a row number that is 4 times the current row number." Can't figure out how to do it though.

CodePudding user response：

try:

=INDEX(ROW(Sheet2!A:A) * 4)

CodePudding user response：

Use filter(), like this:

=filter( 
  Sheet2!A1:A, 
  match( 
    row(Sheet2!A1:A), 
    4 * sequence(rows(Sheet2!A1:A)), 
    0 
  ) 
)

This formula gets every fourth value from Sheet2 in one go.