SOLVED

# Reference data from sheet1 to sheet2

Copper Contributor

# Reference data from sheet1 to sheet2

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 (Copper Contributor)
Solution

# Re: Reference data from sheet1 to sheet2

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

# Re: Reference data from sheet1 to sheet2

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.

# Re: Reference data from sheet1 to sheet2

2958 Kensington Avenue 2nd fl.
1 best response

Accepted Solutions
best response confirmed by phimmychan (Copper Contributor)
Solution

# Re: Reference data from sheet1 to sheet2

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