An option is using OFFSET(), putting in Sheet2.A1 =OFFSET(Sheet1.$A$1;ROW()-1;COLUMN()-1), always reference the same cell in sheet1 as the formula is.