» Retrieving a Specific Cell from Multiple Sheets

Problem:

We want to copy the contents of cell A1 from Sheets1-5 into row 2 of the current sheet.
That is, cell C2 of the current sheet is to contain the value stored in cell A1 of Sheet1, cell D2 is to contain the value from Sheet2, and so on.

Solution:

Use the INDIRECT and COLUMN functions as shown in the following formula:
=INDIRECT("Sheet" & COLUMN()-COLUMN($C$2)+1 & "'!$A$1")