Reference data from sheet1 to sheet2

[phimmychan]

I want to reference data from sheet1 to sheet2, with the data sorted by the example format. Is there any formula that works? or does it have to be done manually; such as sheet2!A1='sheet1'!A4?

Ps. Data from sheet 1 has 1000 rows, meaning spread to sheet 2 will be 4000.

[#2 · Accepted solution]

@phimmychan 

=IF(MOD(ROW(),4)=3,INDEX($A$4:$A$104,QUOTIENT(ROW()+4,4))&".1",IF(MOD(ROW(),4)=0,INDEX($A$4:$A$104,QUOTIENT(ROW()+3,4))&".2",INDEX($A$4:$A$104,QUOTIENT(ROW()+4,4))))

=IF(MOD(ROW(),4)=3," Average value",IF(MOD(ROW(),4)=0," Maximum value value",INDEX($B$4:$B$104,QUOTIENT(ROW()+4,4))))

You can try these formulas starting in row1. If you actually want merged cells in the column with "store" then you can manually enter "store" in row1 and "store" in merged rows2 to row4 and then copy rows 1 to 4 and paste into rows 5 to 4000.

[#3]

Hello,
Thank you so much for your brilliant help! Appreciate it! I follow your suggestion and it works.
I'm trying to understand the formula and it will take me a while.

[#4]

2958 Kensington Avenue 2nd fl.