Use Indirect
.
Indirect
allows you to use a string as a reference to a worksheet location. So, if the content of cell A1 is set to 'DaySheet', you can use the formula =Indirect(A1)
!B1 and the result will be whatever is in cell B1 in the DaySheet sheet.
From the support page:
You can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually by using the INDIRECT function to create a user-defined dynamic reference with worksheet cell references as inputs.
EDIT: Might need a mention on how to deal with referencing the current cell.
To drill down from the sheet to the cell, you can either use a string reference (eg "A1") concatenate
d to the indirect reference, or you can use the address
function to do it dynamically. Below is an example of both:
String:
=INDIRECT(CONCATENATE($A$1,"!","B1"))
Dynamic:
=INDIRECT(CONCATENATE($A$1,"!",ADDRESS(ROW(), COLUMN(), 4)))
The first is easiest where you don't copy from too many cells. More than a handful, and I'd go with the second option.
MS help on the Address function.