Increment Column by 5, fill down

[dkwichs]

Hi There,

I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).

Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).

Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1. I've read some posts on incrementing by more than one but couldn't find any help on how to make Columns auto increment while pulling down instead of right.

I hope that was fairly clear, i really appreciate any help or guidance anyone may have. My thoughts have been going to using OFFSET with COLUMNS and possibly ADDRESS but i can't quite seem to get it.

Thanks in advance!

 

[Jonmo1]

Welcome to the board..

Try

=INDEX(10:10,(COLUMNS($A1:A1)-1)*5+5)

Don't change the $A1:A1, it has nothing to do with the location of your data.
The RED 5 represents the Interval (every 5 columns)
The BLUE 5 represents the first column to begin in (column E)

Hope that helps.

 

[dkwichs]

Wow thanks for the fast reply jonmo!

I used the formula and modified it so it pointed to the other sheet but it seems to still only work properly if i drag and fill to the right. Going down just moves down the data sheet as well.

Here is what i mod'd it to:

=INDEX('Sheet1'!24:24,(COLUMNS($A1:A1)-1)*5+5)

Hmm, you've given me a better insight into the formula i should be using though.

 

[Jonmo1]

Oh, I see...Dragging formula DOWN and incrimenting column by 5...right?

=INDEX('Sheet1'!$24:$24,(ROWS(A$1:A1)-1)*5+5)

 

[dkwichs]

Amazing!

Thank you so much this works exactly how I need it to. I'm adapting it right now for all the other data sets I need to put in the summary sheet.

Thanks for your fast replys and great help jonmo, maybe i'll be able to help you out one day! :P

 

[Jonmo1]

Glad to help, thanks for the feedback.