select every nth cell from a different sheet

[mynameismada]

I know that this is probably easy, but I can't find the right formula, so please a little help.

I have 2 sheets. I need the in cell E4(sheet 1) to have the value from B3(sheet 2), then for cell E5(sheet 1) to have the value from H3(sheet 2) and so on. basically from sheet 2 i need every 6th value: B3, H3, N3 etc.

How do i do that? INDEX, INDIRECT?

[benishiryo]

hi there, welcome to the forum. try this in E4:
=INDEX(Sheet2!$B$3:$N$3,1+(ROW(A1)-1)*6)

do note that the part in bold should be your whole range you need to capture. i'm just doing an example up to Column N as you stated.

[mynameismada]

@benishiryo Thanks a lot, it worked with a few modifications =INDEX(Data!$B3:$BC3,1+(COLUMN(A1)-1)*6)


The problem now is that when i drag it, it modifies the row, not the column. so when i drag it, i have:

=INDEX(Data!$B3:$BC3,1+(COLUMN(A1)-1)*6)
=INDEX(Data!$B4:$BC4,1+(COLUMN(A1)-1)*6)

instead of:

=INDEX(Data!$B3:$BC3,1+(COLUMN(A1)-1)*6)
=INDEX(Data!$C3:$BD3,1+(COLUMN(A1)-1)*6) - which is what i need

[Ace_XL]

You do not need relative references for the formula to work.

benishiryo's fomrula works ....and as in his formula suse absolute refernces i.e with two dollar signs as in $A$1 etc

The only relative reference would be Row(A1) which will change to Row(A2), Row(A3) and so on thereby skipping 6 columns (due to ROW(A1)-1)*6) every time you drag down