Cell reference, pick up 2 cells then skip 3 rows....

[ljubo_gr]

Hello dear MrExcel!

Indirect values from sheet1, but it needs to skip 3 rows, pattern is always 3 rows, and there will always be 2 rows to pick up

Thanks in advance!

[/IMG]

[/IMG]

 

[Special-K99]

How about if we just ignore blank cells in the column?

in Sheet2:A2 (as you haven't mentioned where the output should go)
=IFERROR(INDEX(Sheet1!$A$2:$A$1000,SMALL(IF((Sheet1!$A$2:$A$1000<>""),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter

Copy down for as many rows as you have in Sheet1 column A

 

[ljubo_gr]

Sorry, i google it, i know for INDEX(SMALL---, problem is, sorry i didn't mentioned, there is other data in between.

 

[Special-K99]

Or if you really want to use INDIRECT()

in Sheet2!A2
=INDIRECT("Sheet1!A"&QUOTIENT((ROW()-2),2)*5+MOD((ROW()-2),2)+2)

 

[ljubo_gr]

There will be 2000 rows, what is better? Indirect or some kind of Index(small solution?

 

[Special-K99]

Well you just said you cant use INDEX(...SMALL()...) as there is data in between.

 

[ljubo_gr]

QUOTIENT i never knew, THANKS Special-K99, please help, I need to start from Sheet1!A26 pick up 18 rows A43 then jump 52 rows, then A78:A95 then jump 52....

 

[Special-K99]

Looks like this will work

=INDIRECT("Sheet1!A"&QUOTIENT((ROW()-26),18)*26+MOD((ROW()-26),18)+26)

Formula seems to be

=QUOTIENT((ROW()-x),z)*y+MOD((ROW()-x),y)+x

where
x= is start output row
y is number of jumps
z= is number of rows to pick up

 

[ljubo_gr]

Looks like this will work

=INDIRECT("Sheet1!A"&QUOTIENT((ROW()-26),18)*26+MOD((ROW()-26),18)+26)

Formula seems to be

=QUOTIENT((ROW()-x),z)*y+MOD((ROW()-x),y)+x

where
x= is start output row
y is number of jumps
z= is number of rows to pick up

I'm so sorry, not working! Data are in Sheet1!A26:A43-A78:A95-A130:A147..... On Sheet2!A2 is output column, no blanks, no jumps, just your formula in A2 copy down 1000 rows. After row 18(A19) it isn't "jump" from Sh1A43 to Sh1A78.

Diff between A43-A78 is 35 rows, I was wrong(52), sorry :B

 

[Special-K99]

A43-A78 is 36 rows

You have the general formula now so you can do it yourself.

A26 - A78 - A130 is a jump of 52 each time
A26-A43 is 36 rows
A2 is the output row
So just replace the letters with the cell references above

=QUOTIENT((ROW()-x),z)*y+MOD((ROW()-x),y)+x

where
x= is start output row
y is number of jumps
z= is number of rows to pick up

Am starting to lose interest in this when you have the means to do it yourself but havem't done so.