Bring in information from consecutive rows to every 6th row

[ozbeachbum]

Hi,
I am looking for a formula that I can copy down from '[01 REGISTER holding co.xlsx]check'!$ES70 to bring the information from consecutive rows in '[19 REGISTER app data.xlsx]App - Web Site Data'!$F70.
Column ES only has blank spaces between every 6th row.
EG;
'[19 REGISTER app data.xlsx]App - Web Site Data'!$F70 into '[01 REGISTER holding co.xlsx]check'!$ES70
'[19 REGISTER app data.xlsx]App - Web Site Data'!$F71 into '[01 REGISTER holding co.xlsx]check'!$ES76
'[19 REGISTER app data.xlsx]App - Web Site Data'!$F72 into '[01 REGISTER holding co.xlsx]check'!$ES82
and so on.
Any assistance greatly appreciated.
Dave.

 

[Joe4]

If you are simply trying to get rid of the blank rows, why not just use the new Filter function (not to be confused with the old interactive Filters and Advanced Filters) to bring the data over?
Just tell it to filter out the rows what are blank (i.e. <>"")

See here more more details on the new Filter function: FILTER function - Microsoft Support

 

[ozbeachbum]

Thanks for your reply.
I went to the link you sent and watched the video, I also did some more research, but still can't come up with a solution.
From everything I watched and read, it seems as though it would work if I was doing the reverse of what I need.
To simplify I have just used the NUMBER of the WORKBOOK and Range, rather then the full title, I am sure that you will get the gist.
19 has data on consecutive rows 70:113
01 requires the data from 19 to go on non consecutive rows (5 blank rows between) every 6th row 70:328.
If you are able to provide me a little more advice it would be greatly appreciated.

 

[Peter_SSs]

I know it is old, but if you still need help with this, put this formula in ES70 of '[01 REGISTER holding co.xlsx]check' and drag down.

=LET(r,ROWS(ES$70:ES70),IF(MOD(r,6)=1,INDEX('[19 REGISTER app data.xlsx]App - Web Site Data'!F$70:F$113,INT(r/6)+1),""))