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.


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.)

| improve this answer | |
  • Thanks! What's wrong with the array formulas? I have a sheet with a bunch of columns with this data, I need to have each day on a row vs a column, and the person's name needs to follow it for each row. – Douglas Gaskell Oct 11 '15 at 3:51
  • 1
    It looks repetitive, and arrayformula is unnecessary, since arrays auto-expand. E.g., ={A2;B2;C2} expands to three cells. – user79865 Oct 11 '15 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.