=INDIRECT(ADDRESS(C4*5+5,4))

Just add the reference to the other worksheet.HTH

>Hello all
>
>I will explain this as simply as possible
>
>I have 2 excel workbooks "Leave Journal" and "Timecard"
>
>The leave journal has every pay period for the year ;
>
>Week 2 mon tue wed thur fri ....
>annual
>Personal
>Sick
>
>I want the Timecard to automatically fill in the leave for the pay period
>
> in out tot annual Personal sick
>mon
>tue
>wed
>thur
>fr...
>
>The column reference is static and I figured out that annual row will always equal week#*5+3, personal row will always equal week#*5+4 and sick row will always equal week#*5+5 but I can't get the formula to work
>
>i.e.
>this is the code in the timecard where I am trying to get if any leave was taken
>
>leave1.xls is the leave journal and "Stephen McLaughlin" is the sheet and C4 equals the week number on the leave.xsl
>
>&& C4 = 2 'the second week'
>
>='[LEAVE1.XLS]Stephen McLaughlin'!d(C4*5+5)
>
>I want it to return the value from {LEAVE1.XLS]Stephen McLaughlin'!d15
>and I keep getting a error #NAME?
>
>Thanks for any and all help