1

I have the following formulas on the third sheet of my workbook. They are simply cell references to the first sheet; on my 'output' page, I want to display every 17th cell of the 'input' sheet. However when I drag the formula down, it goes up in 2's. Any help gratefully appreciated.

=Input!B2   =Input!F10   =Input!F6   =Input!H11   =Input!F4   =Input!F13   =Input!F8
=Input!B19  =Input!F27   =Input!F23  =Input!H28   =Input!F21  =Input!F30   =Input!F25

(The use case is that I have "records" on the 'Input' sheet that are 17 rows high, and I want to display summary data on the 'Output' sheet in a one-row-per-record arrangement.)

Improve this question
4
  • 1
    The alignment makes the example hard to follow, but what you show seems to contain references to cells 17 rows apart. The are no formulas shown, just cell references. Can you clarify what's there, what you do, and what you get when you do what you do?
    – fixer1234
    Dec 30, 2014 at 3:21
  • Thanks for your comment. You are correct, I should've said cell references. I want to display the cell B2 from the first sheet, on A1 on the 3rd sheet. Then I want to jump 17 spaces on the first sheet, and display cell B19, and have that display on A2 on the 3rd sheet. And when I drag down, I would like A1, A2, A3 to increase by 17 each time on their own.
    – chris
    Dec 30, 2014 at 17:20
  • Does teylyn's answer give you what you need? If not, please clarify what doesn't work or what you don't understand. If it answers the question, you can click the checkmark to indicate that the problem has been solved.
    – fixer1234
    Dec 30, 2014 at 17:29
  • As an example, on sheet 1 of my workbook I have a name in cell B2, followed by phone number of the same person in F6 and lots of other information in the cells between. I would like to extrapolate only the name and phone number and display them on sheet 3 in adjacent cells. Then carry that on down the page on sheet 3. If I drag my current cell references, they just go up in 2's. Teylyn's solution certainly works within the same sheet, and if I can get it working I will let you both know. your help is appreciated.
    – chris
    Dec 30, 2014 at 17:34

1 Answer 1

Reset to default
2

This formula will return A17. When draged down it will return the values in A34, A51, A68, etc. i.e. every 17th row.

=INDEX(A:A,(ROW()*17))

If you want to start in a different row, say row 10, you need to subtract the respective number from the starting row, like 7. 

=INDEX(A:A,(ROW()*17)-7)
Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .