Make forumla lookup value rather than forumla

Ok here's the deal. In column C("enter time" tab) I have a drop down box of numbers. In column D("enter time" tab) I have =LOOKUP([@[CONTROL '#]],Sheet1!$A:$A,Sheet1!$D:$D). My problem is that Sheet1, column A is ='Z:\Database\[CONTROL NUMBERS OFFICIAL FILE.xls]Sheet1'!B3. So when the first formula looks up a certain control number in sheet 1 col A all it sees is a bunch of formulas rather than the value the formula looked up. When I look at sheet 1 column A it shows me the value I want, but my lookup formula on the "enter time" tab doesn't see the value it only sees the formula and therefore doesn't find the number it's looking for from column C("enter time" tab) and does not return a value.
I hope this makes sense and there is a way to make it all work. Thanks for your help!

Thanks for the suggestion! I had never heard of that function before but it sounds like it may be the key to what I'm trying to do, unfortunately I can't figure out how to make it work. I think the indirect formula should go somewhere in column D("enter time" tab) but I just don't know.

Files attached. Thanks for helping! I've spent way more time on this that I should have.

It gives a #REF! error.

Oops, I didn't type the whole formula. That seems to work great! If you don't mind, please explain how your formula is different than what I was using. What significance does the ,4,0 have?