Depends on how you have set up your worksheets. One way of doing this is with the help of the INDIRECT formula. The only thing is the workbook from which the data is being picked should be open in-order for the data to be displayed correctly.

So for example, =INDIRECT("'C:\Users\User\Excel Downloads\[ReArrUpdate.xlsm]Sheet"&(DAY(TODAY())-1)&"'!$B$3")