OK, if both workbooks are open then first of all you won't need the path so
this should be enough

=INDIRECT("[HISTORICALWORKSHEET2.xls]"&A5&"!$U$6")

note that it will retrun a ref error when the workbook is closed and the
link I posted have some solutions for that

Regards,

Peo Sjoblom

" wrote:

All the workbooks are open when I am doing this. It is giving me a
syntax error when I try to use a cell name. If I change the A5 to the
actual name of the worksheet "company 1" then it works