Forum Discussion

phimmychan's avatar
phimmychan
Copper Contributor
Mar 13, 2023
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...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Mar 13, 2023

    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.

     

Resources