Google-sheets – Dragging functions through multiple cells with spaces in Google Spreadsheets

google sheets

I have a function that needs to grab data from 1 row at a time on another worksheet. However on the worksheet the function is on, I have extra spaces and dragging the function down causes it to iterate the number of those rows instead of one at a time.

Picture:
enter image description here

How do I get the behavior I want? I need to drag these functions down through ta few thousand rows, and would need it to iterate the row# on teh function automatically.

Best Answer

To refer to B2 from F2, then to B3 from F9, then to B4 from F16... use indirect references: 

=indirect("Filled In Responses!B"&(2+(ROW()-2)/7))

(I also think you are doing something inefficient with Arrayformula there, but that's another thing.)

Related Topic