Reference data from sheet1 to sheet2

New Contributor

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.



3 Replies
best response confirmed by phimmychan (New Contributor)


=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.

reference data from sheet1 to sheet2.JPG


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.
2958 Kensington Avenue 2nd fl.