Forum Discussion
Solved
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. D...
=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.
=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.
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.
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.